HAVING — Filtering Groups
The marketing team wants "customers who have bought more than three items in total". You cannot write WHERE SUM(quantity) > 3, because when WHERE runs, the totals do not exist yet. HAVING is WHERE's partner for the moment after grouping.
The rule
WHERE filters rows before grouping; HAVING filters groups after.
| order_id | customer_id | product_id | quantity | status |
|---|---|---|---|---|
| 101 | 1 | 1 | 2 | shipped |
| 102 | 2 | 3 | 1 | pending |
| 103 | 1 | 2 | 5 | shipped |
| 104 | 3 | 1 | 1 | cancelled |
| 105 | 3 | 2 | 3 | shipped |
SELECT customer_id, SUM(quantity) AS items
FROM orders
GROUP BY customer_id
HAVING SUM(quantity) > 3;The groups are customer 1 (7 items), customer 2 (1) and customer 3 (4). HAVING keeps groups whose total is over 3: customers 1 and 3.
The full order the database follows now makes sense of everything in this section:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- WHERE sees individual rows, so it can test
statusbut notSUM(quantity). - HAVING sees whole groups, so it tests aggregates, or the grouping columns themselves.
- ORDER BY runs last and can sort by an aggregate or its alias:
ORDER BY items DESC.
Some databases (MySQL, SQLite) also let HAVING use the SELECT alias, as in HAVING items > 3, but PostgreSQL and SQL Server do not. Repeating the aggregate works everywhere.
The shortcut
For each condition in the question, ask: is it about one row, or about a group's total? One row → WHERE. A group's total, count or average → HAVING.
Split the conditions
"shipped" is about each order row. "more than 3 items" is about a customer's total.
Notice the answer changed from the first example: filtering rows first shrank customer 3's total. Putting a condition in the wrong clause does not always cause an error — sometimes it quietly changes the answer.
Drills
Answer fast — trust the rule.
Why is SELECT customer_id FROM orders WHERE SUM(quantity) > 3 GROUP BY customer_id; an error?
Which clause should hold status <> 'cancelled' in a per-customer total?
Which customers does SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 2; return?
In what order does the database logically process these clauses?
With WHERE status = 'shipped' added before GROUP BY customer_id HAVING SUM(quantity) > 3, which customers remain?
Remember
- WHERE filters rows before grouping; HAVING filters groups after.
- One row → WHERE; a group's total → HAVING.
- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.