SQL Tutorial · Aggregation and Functions · Lesson 15 of 36
SQL HAVING
Filter groups after aggregation with HAVING.
WHERE vs HAVING
WHEREfilters rows before grouping.HAVINGfilters groups after aggregation, so it can use aggregate functions.
-- Customers with more than one order that wasn't cancelled
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY orders DESC, customer_id;
Output
customer_id | orders 1 | 3 2 | 2 3 | 2 4 | 2
Clause order
SQL clauses always appear in this order — and are logically processed in the order on the right:
| Written | Processed |
|---|---|
SELECT |
1. FROM / JOIN |
FROM |
2. WHERE |
WHERE |
3. GROUP BY |
GROUP BY |
4. HAVING |
HAVING |
5. SELECT |
ORDER BY |
6. ORDER BY |
LIMIT |
7. LIMIT |
Test yourself
Which clause can filter on COUNT(*) > 1?
WHEREHAVINGORDER BYLIMIT
Show answer
Answer: HAVING