MySql, use group by with a WHERE clause

Question

I want to add a WHERE clause to my statement, but the problem is, when I add the WHERE clause, I get an error "Invalid use of group function".

I also tried to replace the WHERE clause and write the condition into the JOIN .. ON part, but the error is still there.

I want to add the condition so that only the rows " SUM(res.ReservationID) = 2" are returned.

-- works but we only want to get the rows in which the SUM = 2
SELECT ctr.ID, ctr.LastName, ctr.FirstName, SUM(res.ReservationID) as ReservierteSitze
FROM customer as ctr
INNER JOIN reservation AS res ON ctr.ID = res.CustomerID
Group by ctr.ID;

Answer 1

SELECT ctr.ID, ctr.LastName, ctr.FirstName, SUM(res.ReservationID) as ReservierteSitze
FROM customer as ctr
INNER JOIN reservation AS res ON ctr.ID = res.CustomerID
Group by ctr.ID
HAVING ReservierteSitze = 2;

The HAVING clause is like a where clause for the GROUP BY (applies to the groupings)

You can still have a WHERE clause before the GROUP BY clause, but that only applies to the individual rows before the grouping.