Chapter 2 · 8 min
Filtering groups with HAVING
The previous lesson separated WHERE from HAVING in one sentence. This lesson is about why that
separation is structural rather than stylistic. WHERE runs before grouping, so it can only see
individual rows. HAVING runs after grouping, so it can see the aggregate values that grouping
produced.
SELECT customer_id, count(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING count(*) > 3
ORDER BY paid_orders DESC;
Read the clauses in execution order: filter to paid orders, group the survivors by customer, count
each group, then discard groups whose count is three or fewer. Moving the count condition into
WHERE would fail, because the count does not exist yet at that point.
A condition on the group is not a condition on the row
The distinction changes answers, not just syntax. “Customers with more than three paid orders” filters groups. “Paid orders belonging to customers who have more than three paid orders” also filters groups, but it returns the underlying rows rather than the counts. Both start with the same aggregate; they differ in what the outer query selects.
A useful test: if the condition mentions count, sum, avg, min, or max, it belongs in
HAVING. If it mentions a plain column, it usually belongs in WHERE so the engine can discard
rows before doing the expensive grouping.
Aliases and repetition
Postgres does not let you reference a select-list alias in HAVING; the alias is assigned after the
grouping phase. You repeat the aggregate expression instead:
HAVING count(*) FILTER (WHERE status = 'refunded') > 10
FILTER is the clean way to count a subset inside one grouped query. It keeps the row filter and
the group filter in their proper clauses instead of hiding a CASE inside WHERE.
HAVING without GROUP BY
HAVING is allowed without a GROUP BY. In that case the whole result is one group, so the query
returns either one row or no rows. That form is useful for a single global check:
SELECT count(*) AS orders
FROM orders
HAVING count(*) > 100;
It is easy to mistake for a row filter. If the intent is “return the rows of a table that has more
than 100 rows”, the answer is not this query; it is a plain SELECT with a subquery condition.
What to say in an interview
State the two phases explicitly: rows are filtered first, groups are filtered second. Then name
which condition belongs to which phase. An interviewer who hears that sentence knows you are not
guessing at clause order, and the SQL that follows is much less likely to contain an aggregate in
WHERE.