HAVING Clause
HAVING is for filtering groups, not raw rows. Think of it as WHERE for aggregate output.
WHERE vs HAVING
| Clause | Evaluated on | Typical use |
|---|---|---|
WHERE | Raw rows before grouping | WHERE status = 'paid' |
HAVING | Grouped rows after aggregation | HAVING SUM(total_amount) > 10000 |
Core Syntax
SELECT customer_id, SUM(total_amount) AS total_spend
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
HAVING total_spend >= 5000;
Practical Example
Active High-Value Customers
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spend
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 5
AND SUM(total_amount) >= 10000
ORDER BY total_spend DESC;
| customer_id | order_count | total_spend |
|---|---|---|
| 1024 | 11 | 24300.00 |
| 2031 | 7 | 13120.00 |
| 4120 | 6 | 10205.00 |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
Using aggregate in WHERE | Syntax error | Move aggregate predicates to HAVING |
Putting non-aggregate filters in HAVING only | Unnecessary processing | Keep row filters in WHERE, group filters in HAVING |
| Forgetting aliases in long expressions | Hard-to-read queries | Alias aggregates and reuse aliases in HAVING (MySQL allows it) |
Quick Reference
SELECT k, COUNT(*) c FROM t GROUP BY k HAVING c > 10;
SELECT k, SUM(v) s FROM t WHERE status = 'ok' GROUP BY k HAVING s >= 1000;
SELECT k, AVG(score) a FROM t GROUP BY k HAVING a BETWEEN 70 AND 90;