HAVING

Filters groups after GROUP BY, using aggregate values.

Syntax

SELECT key, aggregate(...)
FROM ...
GROUP BY key
HAVING aggregate_condition;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Use HAVING for conditions on aggregates: HAVING count(*) >= 5, HAVING sum(amount) > 10000.
Conditions on plain columns belong in WHERE: it removes rows earlier and the result is the same (and faster).
In PostgreSQL, HAVING cannot reference select-list aliases — repeat the aggregate expression.
HAVING without GROUP BY treats the whole table as one group.

Examples

Customers with at least 3 orders starter

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY orders DESC, customer_id;

Groups with fewer than three orders are removed after counting.

WHERE and HAVING together shop

SELECT customer_id, count(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING count(*) >= 20
ORDER BY delivered_orders DESC, customer_id;

WHERE keeps delivered orders only; HAVING then keeps customers with 20 or more of them.

Pitfalls

Using the alias in HAVING (HAVING orders >= 3): column "orders" does not exist. Repeat count(*).
Putting row-level conditions in HAVING: it works only if the column is grouped, and it is slower and less clear.
Putting aggregate conditions in WHERE: aggregate functions are not allowed in WHERE.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: Allows select aliases in HAVING.
BigQuery: Allows select aliases in HAVING.