(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 ```

Accepted Answer

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
```

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

Solution

Problem