-1

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;
Sripriya V
  • 295
  • 3
  • 8
normalUser221
  • 41
  • 7
  • 3
    Because WHERE is evaluated before GROUP BY, so you can't use aggregate functions there. – Paul Spiegel Jun 13 '17 at 19:21
  • 1
    Still struggling? See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 13 '17 at 19:27
  • Does this answer your question? [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – philipxy Jan 18 '23 at 04:06

2 Answers2

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

Paul T.
  • 4,703
  • 11
  • 25
  • 29
0

having(Aggregation function condition processing)

kui.z
  • 74
  • 2