HAVING for filtered groups

Concepts covered: Group Filtering, Execution Order

WHERE and HAVING both filter, and they filter different things at different moments. WHERE runs before grouping and decides which rows are allowed into the groups at all. HAVING runs after the groups and their aggregates exist and decides which groups make it into the result. A condition on a raw column belongs in WHERE; a condition on an aggregate, such as a count above a threshold or a total below a target, can only be checked once the aggregate has been computed, and that is the work HAVING does.

Regions with more than 3 orders

The orders table below holds 8 orders: 4 in North, 3 in South and 1 in East. A report that lists only regions with more than 3 orders groups by region, counts, and then keeps the groups whose count clears 3.

Three groups form, with counts 4, 3 and 1. HAVING COUNT(*) > 3 tests each group's count: North passes with 4, South fails with exactly 3 because the comparison is strict, East fails with 1. The result is 1 row. Change the operator to >= and South joins it. WHERE COUNT(*) > 3 in the same query is an error, because when WHERE runs no counting has happened yet; the groups it would need to count do not exist.

The order the database evaluates clauses

A query is written as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and evaluated as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Two consequences follow. WHERE cannot see aggregates because they have not been computed when it runs. HAVING cannot see a column alias from SELECT in standard SQL, because SELECT runs after it. So HAVING order_count > 3 fails on PostgreSQL, SQL Server and Trino, even though HAVING COUNT(*) > 3 works on all three. MySQL and SQLite accept the alias as an extension. Repeating the aggregate expression inside HAVING is the portable spelling.

The animation below combines both filters. 5 orders carry a status: 4 are completed and South order 2 is pending. WHERE status = 'completed' removes the pending order before grouping, so South enters the grouping stage with 1 order and North with 3. HAVING COUNT(*) > 2 then removes South. The sequence of the 2 filters matters: a WHERE that removes rows changes the counts that HAVING goes on to test.

SELECT region, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY region
HAVING COUNT(*) > 2

The result is 1 row, North with 3 completed orders. Moving the status test out of WHERE and into HAVING is possible only in a form such as HAVING SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) = 0, which asks a different question (regions with no pending orders at all) and does more work, since every row reaches the grouping stage. Filtering rows as early as possible is cheaper and, more often than not, what the report means. Assemble the same query from its pieces below; 1 tile does not belong.

Build the query that counts completed orders per region and keeps only regions with more than 2 of them. The row filter comes before grouping, the group filter after it, and 1 tile is a distractor that no valid query uses.

The leftover tile, WHERE COUNT(*) > 2, is the line a reader writes when they think of WHERE as the only filter. It fails on every engine with a message about aggregates not being allowed in WHERE, and the fix is always the same: move the condition into HAVING.

Which filter to reach for

WHERE

• Runs before GROUP BY, on individual rows
• Tests raw columns: status = 'completed', amount > 100
• Shrinks the data the grouping has to process
• Cannot reference COUNT, SUM or any other aggregate

HAVING

• Runs after GROUP BY, on finished groups
• Tests aggregates: COUNT(*) > 2, SUM(amount) < 1000
• Removes whole groups from the result
• Can test a grouping column too, but WHERE does that cheaper

A condition that could go in either place, such as region = 'North', belongs in WHERE: the result is identical and the engine groups fewer rows. The practice orders table has a region and a profit per order; complete the query that keeps only regions with more than 1 order.

Every grouped query in this lesson follows the same skeleton: rows in, WHERE trims them, GROUP BY sets the grain, aggregates compute 1 value per group, HAVING trims the groups. The habits below are the ones that keep that skeleton correct on a table too large to check by eye.

Do

• Put row conditions in WHERE so fewer rows reach GROUP BY
• List every non-aggregated SELECT column in GROUP BY
• Pick COUNT(*), COUNT(col) or COUNT(DISTINCT col) by the question being asked
• Decide what a NULL means before averaging a column that holds them
• Repeat the aggregate expression in HAVING instead of relying on a SELECT alias

Avoid

• Write an aggregate inside WHERE; it runs before any group exists
• Expect MAX(price) to tell you which row holds that price
• Treat AVG as SUM divided by COUNT(*) when the column has NULLs
• Group by a column that is distinct per row and expect a summary
• Assume COUNT(col) equals COUNT(*) on a nullable column

About this lesson

This section is part of the Aggregating: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.