Filtering Groups with HAVING

You can summarize per group. Now you want to keep only the interesting groups — "regions with revenue over $1000", "customers with more than 10 orders", "products averaging below 3 stars." That is HAVING: a filter that runs after aggregation, on the group summaries themselves. The hardest part is keeping it distinct from WHERE, so that is what this page nails down.

Two filters, two moments

WHERE and HAVING both filter — but at different stages of the query.

• WHERE runs before grouping. It filters individual rows, so it can only see raw columns, never group totals.
• HAVING runs after grouping. It filters whole groups, so it can see aggregates like SUM(amount).

That single diagram is the whole mental model. WHERE is upstream of the groups; HAVING is downstream. A condition that mentions an aggregate must go in HAVING, because the aggregate does not exist yet when WHERE runs.

HAVING in action

"Which regions brought in more than $150 of revenue?" The condition is about SUM(amount) — a group total — so it belongs in HAVING.

North (60) and any other small region are dropped after their totals are computed. You cannot express this with WHERE, because at WHERE time there is no per-region total to compare against.

Using WHERE and HAVING together

They are not rivals — analysts routinely use both in one query, each doing its proper job: WHERE narrows the rows that enter the groups; HAVING selects which resulting groups survive.

Read it as a pipeline: "Of the paid orders, group by region, and show only regions whose paid revenue exceeds $150." The refunded rows never enter the groups at all — and that changes the totals HAVING then judges.

The classic mistake

Putting an aggregate in WHERE is the error everyone makes once:

-- ❌ Wrong: SUM() does not exist yet when WHERE runs
SELECT region, SUM(amount)
FROM orders
WHERE SUM(amount) > 150
GROUP BY region;

DuckDB rejects this because WHERE filters rows before any grouping or summing happens. Move the aggregate condition to HAVING and it works. The reverse mistake — putting a plain row condition in HAVING — usually runs but is wasteful: filter rows in WHERE so fewer rows ever reach the grouping step.

A quick decision rule

Ask: does my condition mention an aggregate?

• No (it is about a single row's column) → WHERE.
• Yes (it is about a group total/average/count) → HAVING.

Check your understanding

You have now mastered summarization: aggregates, grouping, multi-level subtotals, and group filtering. Next comes a different superpower — window functions, which summarize without collapsing the rows.