Fix "aggregate functions are not allowed in WHERE" in PostgreSQL
ERROR: aggregate functions are not allowed in WHERE
WHERE looks at one row at a time, before rows are grouped. SUM(), COUNT() and AVG() need a whole group of rows, so at the WHERE stage there is nothing for them to add up yet. Group filters belong in HAVING.
Why it happens
- Filtering on a group total, like customers who spent over 300, in WHERE instead of HAVING.
- Comparing each row with an overall average, like amount > AVG(amount).
- Mixing a row filter and a group filter in the same WHERE.
How to fix it
1. Move the condition to HAVING
Fails
SELECT customer_id, SUM(amount) AS total FROM orders WHERE SUM(amount) > 300 GROUP BY customer_id;
Works
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 300;
HAVING runs after GROUP BY, when each group's total is known. It goes after GROUP BY, not before it.
2. Compare with a subquery
Fails
SELECT id, customer_id, amount FROM orders WHERE amount > AVG(amount);
Works
SELECT id, customer_id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);
The subquery works out the average once, then WHERE compares each row with that single number.
3. Keep row filters in WHERE, group filters in HAVING
Fails
SELECT customer_id, COUNT(*) AS completed_orders FROM orders WHERE status = 'completed' AND COUNT(*) >= 2 GROUP BY customer_id;
Works
SELECT customer_id, COUNT(*) AS completed_orders FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING COUNT(*) >= 2;
status is a plain column, so it stays in WHERE and removes rows first. COUNT(*) is about the group, so it moves to HAVING.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- High-value repeat buyersGROUP BY · HAVING
- Customers with a big orderIN (subquery) · EXISTS
- Duplicate card chargesGROUP BY · HAVING to find duplicates
- Paid above department averageAVG() OVER · compare to group