◀ Course contents Part 2 · Module 2-03

HAVING & Filter Order

Two clauses that look like filters and are not interchangeable

WHERE cannot see a COUNT. That one fact needs explaining rather than memorising, and the explanation is the order a database really works through a query — which turns out to answer half a dozen other questions at the same time, including why an alias works in one clause and fails in another.

Ready?

A Query Does Not Run in the Order You Write It

You write SELECT first. The database does it almost last. Every confusing thing in this module — and several from the last two — falls out of one sequence, which is worth learning as a sentence rather than as a list of rules:

SELECT is written first and evaluated fifth. An alias is a name created in step 5, so nothing in steps 2 to 4 can see it — and an aggregate is computed in step 3, so nothing in step 2 can see that either.

Read that sequence and two rules you have already met stop being arbitrary:

WHERE cannot use an alias

From module 1-02. The alias is made in step 5; WHERE ran in step 2.

WHERE cannot use an aggregate

The piles are made in step 3. When WHERE runs there is no COUNT to test, because there is nothing yet to count.

And the new one, which is what this module is for: a filter that needs an aggregate has to run after step 3. That is HAVING, at step 4. It is not a second flavour of WHERE; it is a filter at a different point in the pipeline, operating on different things.

Quick check

Why does WHERE COUNT(*) > 3 fail?

WHERE Filters Rows, HAVING Filters Piles

Both clauses take a condition and throw away what fails it. The difference is what they are throwing away.

SELECT user_id, COUNT(*) AS n
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

Make a pile per user, count each pile, discard the piles of two or fewer. The rows inside a surviving pile are untouched — HAVING never removes some of a group, it removes the group or keeps it whole.

There is a simple test for which clause a condition belongs in: can a single row answer it on its own?

WHERE

One row can answer it

status = 'paid', amount > 100, country IN ('UK','US'). Look at the row, decide, move on.

HAVING

It needs the whole pile

COUNT(*) >= 2, SUM(amount) > 50, AVG(price) > 20. No single row knows the answer.

A query can and often should have both, each doing its own job:

SELECT user_id, COUNT(*) AS n
FROM orders
WHERE status = 'paid'      -- rows: only real purchases
GROUP BY user_id
HAVING COUNT(*) > 1;       -- piles: only repeat buyers

Leave the GROUP BY off entirely and the whole table becomes one group, so a bare HAVING tests that. It is unusual in a report and genuinely useful as a scheduled check — return a row only if something is wrong is an alert that stays silent until it should not.

Everyday example

Sorting a bag of post into piles by street. Throw away anything not addressed to this city is a decision you make on each letter as it passes through your hands — before any piles exist. Ignore streets with fewer than three letters is a decision you can only make once the sorting is finished and you can see how tall each pile is.

Quick check

Which clause should hold "only count orders from 2024"?

The Dangerous Case Is the One That Runs Either Way

If you put an aggregate in WHERE, the database stops you. That is a good error: loud, immediate, impossible to ship.

The costly mistake is the condition that is legal in both clauses — because then there is no error at all, just two queries that return different numbers and look equally reasonable.

-- A: filter the rows, then count what survived
SELECT user_id, COUNT(*) AS n
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) > 1;        -- 8 users

-- B: count everything, then filter the piles
SELECT user_id, COUNT(*) AS n
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;        -- 9 users

One user separates them. They have two orders; one was refunded. Query A asks "who has bought more than once", which they have not. Query B asks "who has ordered more than once", which they have. Both are correct answers to different questions, and only one of them is the question that was asked.

Neither query announces which one it is. There is no warning, no discrepancy in the plan, nothing to notice in review — the only way to get it right is to decide, deliberately, whether the condition is about the rows going in or the groups coming out.

The churn number that was too good

A retention report counted customers with at least two months of activity. The cancellation filter had drifted from WHERE into HAVING during a refactor, so cancelled months were still counted towards the two-month threshold and then removed afterwards. Retention improved by four points overnight and nobody questioned it for a quarter, because the query ran clean and the direction was the one everyone hoped for.

HAVING also takes AND and OR, with the same bracket rules as WHERE from module 1-02, and each condition can test a different aggregate:

HAVING COUNT(*) >= 2 AND SUM(amount) > 50

Quick check

A report should show products ordered at least 3 times, counting only paid orders. Where does status = 'paid' go?

Aliases, and Rounding at the Wrong Moment

The alias you write in the SELECT list is created at step 5. So:

`WHERE n > 3`

Step 2. The name will not exist for another three steps. Rejected everywhere.

`HAVING n > 3`

Step 4, still one step early. Postgres rejects it: column "n" does not exist. MySQL allows it, which is how the habit forms.

`ORDER BY n DESC`

Step 6, after the SELECT list has been computed. Safe, standard, and the reason ORDER BY feels different from the other clauses.

`GROUP BY 1`

Not an alias but an output position, which Postgres does accept. Handy when the grouping expression is long.

So: repeat the aggregate in HAVING, and use the alias freely in ORDER BY. It costs a few characters and removes a whole class of "it worked on my laptop".

One more trap, easy to miss because it is about arithmetic rather than clauses. Round for display, compare on the raw value.

-- Wrong: 20.004 rounds to 20.00 and slips through
HAVING ROUND(AVG(price), 2) > 20

-- Right: test the real average, round only what is shown
SELECT category, ROUND(AVG(price), 2) AS avg_price
...
HAVING AVG(price) > 20

The wrong version produces a report where a row shows exactly the threshold value it was supposed to beat — which reads as a rounding display quirk and is in fact a row that should not be there at all.

Quick check

Which of these is portable to any SQL database?

0 of 9 completed

Loading the tables…

To do

WHERE cannot help here. It runs before the piles exist, so it has no COUNT to test. HAVING runs after the grouping and filters the summary rows.

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
HAVING COUNT(*) > 5;

Read it as: make the piles, count each one, then throw away the piles that are too small.

Your task: from orders, return each user_id with more than two orders, and their count as n, ordered by user_id.

query.sql

SELECT
FROM orders
GROUP BY user_id
HAVING
ORDER BY user_id;

PostgreSQL

Hint

SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id HAVING COUNT(*) > 2 ORDER BY user_id;

Output

To do

Any aggregate can be tested, not just COUNT. The condition is written the same way it would be in a WHERE — a comparison — it just happens to be about a value the group produced.

Your task: from orders, return each user_id who has spent more than 100 in total, with their total as spent, biggest spender first.

query.sql

SELECT
FROM orders
GROUP BY user_id
HAVING
ORDER BY

PostgreSQL

Hint

SELECT user_id, SUM(amount) AS spent FROM orders GROUP BY user_id HAVING SUM(amount) > 100 ORDER BY spent DESC;

Output

To do

Your task: from products, return each category whose average price is above 20, with that average rounded to two decimals as avg_price, ordered by category.

Round in the SELECT list for display; test the raw AVG(price) in the HAVING. Rounding before comparing is how a value of 20.004 sneaks past a threshold of 20.

query.sql

SELECT
FROM products
GROUP BY category
HAVING
ORDER BY category;

PostgreSQL

Hint

SELECT category, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category HAVING AVG(price) > 20 ORDER BY category;

Output

To do

This is the exercise the module exists for. Consider two queries that differ by which clause the status test lives in:

WHERE status = 'paid'   -- drop unpaid ROWS, then count what is left
HAVING COUNT(*) > 1    -- count ALL rows, then drop small GROUPS

They are not two styles of the same thing. The first changes what gets counted; the second changes which counts survive. Put both in one query and each does its own job.

Your task: from orders, counting paid orders only, return each user_id with more than one such order and the count as n, ordered by user_id.

query.sql

-- One condition about a row, one about a group. Both are needed.
SELECT
FROM orders
WHERE
GROUP BY user_id
HAVING
ORDER BY user_id;

PostgreSQL

Hint

SELECT user_id, COUNT(*) AS n FROM orders WHERE status = 'paid' GROUP BY user_id HAVING COUNT(*) > 1 ORDER BY user_id;

Output

To do

A condition that could live in either clause is the dangerous one, because both versions run and they disagree.

Ask for users with more than one order, without any status filter, and the refunded and pending rows count towards the total. Nine users clear the bar. Filter to paid rows first, as the last exercise did, and eight do.

Your task: the unfiltered version. From orders, with no WHERE at all, return each user_id with more than one order and the count as n, ordered by user_id — then compare it to what you just wrote.

query.sql

-- No WHERE this time. Every order counts, whatever its status.
SELECT
FROM orders
GROUP BY user_id
HAVING
ORDER BY user_id;

PostgreSQL

Hint

SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id HAVING COUNT(*) > 1 ORDER BY user_id;

Output

To do

HAVING can test any aggregate, including COUNT(DISTINCT ...). "Bought more than one different thing" is a very different question from "ordered more than once", and this is how you ask it.

Your task: from orders, return each user_id who has ordered more than one different product, with that count as products, ordered by user_id.

query.sql

SELECT
FROM orders
GROUP BY user_id
HAVING
ORDER BY user_id;

PostgreSQL

Hint

SELECT user_id, COUNT(DISTINCT product_id) AS products FROM orders GROUP BY user_id HAVING COUNT(DISTINCT product_id) > 1 ORDER BY user_id;

Output

To do

HAVING takes AND and OR exactly as WHERE does, and the bracket rules from module 1-02 apply unchanged. Each condition can test a different aggregate.

Your task: from orders, return each user_id who has both more than two orders and spent more than 100 in total, with the count as n and the total as spent, ordered by user_id.

query.sql

SELECT
FROM orders
GROUP BY user_id
HAVING
ORDER BY user_id;

PostgreSQL

Hint

HAVING COUNT(*) > 2 AND SUM(amount) > 100 — two aggregate conditions joined by AND, just like a WHERE.

Output

To do

Leave the GROUP BY off and the whole table is treated as one single group — which is exactly what an aggregate did in module 2-01. A HAVING then tests that one group, and the query returns either one row or none.

It is rare in reports and genuinely useful in checks: "return a row only if something is wrong" is a data test you can schedule.

Your task: return COUNT(*) as n from orders, but only if there are more than 20 orders in the table.

query.sql

-- No GROUP BY. The whole table is the group.
SELECT
FROM orders
HAVING

PostgreSQL

Hint

SELECT COUNT(*) AS n FROM orders HAVING COUNT(*) > 20; — there are 24, so one row comes back. Change 20 to 100 and you get none.

Output

To do

The evaluation order is not a style guide, it is enforced. An alias is created in the SELECT list at step 5, so a clause that runs earlier cannot see it:

HAVING n > 3

ERROR:  column "n" does not exist

Which reads as a strange error until you remember that HAVING is step 4. The name really does not exist yet. Repeat the aggregate instead: HAVING COUNT(*) > 3.

ORDER BY is the one clause where an alias is genuinely safe, because it runs last of all — ORDER BY n DESC is fine and idiomatic. GROUP BY is a special case in Postgres: it will not take an alias either, but it does take an output position, so GROUP BY 1 works.

Some databases — MySQL, and the SQLite this dojo used to run on — accept the alias in HAVING anyway. Code written against them fails on the first real Postgres it meets, which is usually production.

Your task: from users, return each plan with more than three users and the count as n, ordered by plan — writing the HAVING with the full COUNT(*) rather than the alias.

query.sql

SELECT plan, COUNT(*) AS n
FROM users
GROUP BY plan
HAVING       -- the aggregate, not the alias
ORDER BY plan;

PostgreSQL

Hint

HAVING COUNT(*) > 3 — spell the aggregate out. HAVING n > 3 is an error here: the alias is made after HAVING has already run.

Output

The repeat customers

To do

The growth team wants a list of customers worth talking to: people who have genuinely bought more than once, and spent real money doing it. Refunded and pending orders are not purchases, so they should not count towards either test.

Your task: from orders, over paid rows only, return one row per customer who has at least two paid orders and has spent more than 50 in total. Return exactly these three columns in this order:

user_id
orders — how many paid orders they have
spent — their total paid amount

Sorted by spent, largest first.

Both filters matter and they belong in different clauses. Getting the status test into HAVING, or the spend test into WHERE, gives you a different set of customers.

query.sql

-- Paid orders only. At least two of them, and more than 50 spent.
SELECT
FROM orders
WHERE
GROUP BY
HAVING
ORDER BY

PostgreSQL

Hint

WHERE status = 'paid', then GROUP BY user_id, then HAVING COUNT(*) >= 2 AND SUM(amount) > 50, then ORDER BY spent DESC.

Output

Sign in to open this module

HAVING & Filter Order

A Query Does Not Run in the Order You Write It

WHERE cannot use an alias

WHERE cannot use an aggregate

Quick check

WHERE Filters Rows, HAVING Filters Piles

One row can answer it

It needs the whole pile

Everyday example

Quick check

The Dangerous Case Is the One That Runs Either Way

The churn number that was too good

Quick check

Aliases, and Rounding at the Wrong Moment

WHERE n > 3

HAVING n > 3

ORDER BY n DESC

GROUP BY 1

Quick check

Keep only the busy groups

Filter on a total

Filter on an average

The same word in two places

Move the condition, change the number

A threshold on distinct values

Two conditions on one group

HAVING without a GROUP BY

Where an alias exists yet, and where it does not

The repeat customers

Notification

`WHERE n > 3`

`HAVING n > 3`

`ORDER BY n DESC`

`GROUP BY 1`