MySQL HELP aggregate may not appear in WHERE clause

Question

I need help to make my code work The error says, "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Here is my code:

--Query 3
SELECT m.*,b.BikeStopID, r.DateTimeStart, r.DateTimeEnd
FROM Member m INNER JOIN Ride r ON m.MemberID = r.MemberID
INNER JOIN Bike b ON r.BikeID = b.BikeID
WHERE m.MemberID = 4 AND r.DateTimeEnd = (SELECT MAX(r.DateTimeEnd) FROM Ride)

AND r.DateTimeEnd = (SELECT MAX(DateTimeEnd) FROM Ride) --remove r. — Lukasz Szozda

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-01-13T16:47:13

Use a table alias in the subquery:

WHERE m.MemberID = 4 AND
      r.DateTimeEnd = (SELECT MAX(r2.DateTimeEnd) FROM Ride r2)

The expression r.DateTimeEnd in your query refers to the r in the outer query -- hence the error.

If you are only looking for the latest row, though, I would more likely write:

SELECT m.*, b.BikeStopID, r.DateTimeStart, r.DateTimeEnd
FROM Member m INNER JOIN
     Ride r
     ON m.MemberID = r.MemberID INNER JOIN
     Bike b
     ON r.BikeID = b.BikeID
WHERE m.MemberID = 4 
ORDER BY r.DateTimeEnd DESC
LIMIT 1;

Your version might return no rows if the most recent ride is for a different member.

MySQL HELP aggregate may not appear in WHERE clause

1 Answers