(using join) I get not contained in either an aggregate function or the GROUP BY error

Question

I get the following error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'STATS.VisitorIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I used VISITORIP column in both select lists. I used GROUP BY clause in one of them so I addded VISITORIP to it. But I dont have GROUP BY clause in other select so did not add it. Why do I receive this error?

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "') S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

Answer 1

You need the GROUP BY in the nested subquery because you are trying to get the MAX DATEENTERED for each VISITORIP. VISITORIP exists in the SELECT for that subquery, so you need the GROUP BY in there also in order for it to return the latest date for each visitorip.

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "' GROUP BY S1.VISITORIP) S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

Answer 2

This (nested query) is not valid:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED)
FROM STATS S1 
WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'

It should be:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) AS DATEENTERED
FROM STATS S1 
WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'
GROUP BY S1.VISITORIP

Answer 3

Your Inner select uses MAX on DATEENTERED but does not include a groupby on the VisitorIP.

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED)
   FROM STATS S1
   WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'
   GROUPBY S1.VISITORIP) S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

Answer 4

You're getting the error because your subselect in the JOIN:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'

Is attempting to aggregate "DATEENTERED", but needs to group on "VISITORIP" in order to do so.

Try:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "' GROUP BY S1.VISITORIP