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.