SQL / AGGREGATION WITH GROUP BY
Filtering groups with HAVING instead of WHERE
Filter aggregated results correctly by putting row tests in WHERE and group tests such as SUM or COUNT comparisons in HAVING.
What you will learn
- Keep single-row conditions in WHERE and aggregate conditions in HAVING
- Read grouped queries in execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
- Filter on aggregates that are not in the SELECT list
- Recognize that HAVING can never restore rows that WHERE removed
Understanding Filtering groups with HAVING instead of WHERE
A grouped query runs in a fixed order: rows are read from the tables, WHERE discards individual rows, GROUP BY collects the survivors into groups, the aggregates are computed once per group, and only then does HAVING discard whole groups. WHERE therefore cannot mention SUM or COUNT, because at that moment no group exists yet and each row knows nothing about its neighbours. HAVING sits on the far side of the aggregation step, which makes it the only place a comparison against an aggregate can live.
The two clauses also differ in what they are allowed to see. WHERE sees every column of one row, including columns you never grouped by or selected. HAVING sees only what survives grouping: the grouping keys and aggregates over the group, so a raw ungrouped column there is rejected. The practical consequence is that a condition about a single row belongs in WHERE, where it removes rows before they are ever summed and can still use an index; the same test in HAVING is either illegal or silently lets unwanted rows into the totals.
One more consequence follows from the order: groups are built out of the rows that survived WHERE, so a group always contains at least one row and HAVING COUNT(*) = 0 matches nothing. When a query has aggregates but no GROUP BY at all, the whole filtered table is one implicit group, and HAVING tests that single group; this is why such a query can return zero rows even though a bare aggregate query always returns exactly one.
CREATE TABLE sales (
id integer PRIMARY KEY,
region text,
amount numeric,
status text
);
INSERT INTO sales (id, region, amount, status) VALUES
(1, 'north', 100, 'paid'),
(2, 'north', 250, 'paid'),
(3, 'north', 40, 'void'),
(4, 'south', 300, 'paid'),
(5, 'south', 90, 'void'),
(6, 'east', 500, 'paid'),
(7, 'east', 20, 'paid'),
(8, 'west', 700, 'void');
SELECT region,
COUNT(*) AS paid_orders,
SUM(amount) AS paid_total
FROM sales
WHERE status = 'paid'
GROUP BY region
HAVING SUM(amount) >= 320
ORDER BY paid_total DESC;WHERE decides which rows enter a group, HAVING decides which finished groups survive.
Worked examples
Finding duplicate values
A condition that only makes sense about a whole group: keep the emails that appear more than once.
CREATE TABLE signups (id integer, email text);
INSERT INTO signups (id, email) VALUES
(1, 'ana@example.com'),
(2, 'bo@example.com'),
(3, 'ana@example.com'),
(4, 'cy@example.com'),
(5, 'ana@example.com');
SELECT email, COUNT(*) AS copies, MIN(id) AS first_id
FROM signups
GROUP BY email
HAVING COUNT(*) > 1;Example explained
Line 1GROUP BY email collapses the three ana rows into one group, so COUNT(*) for that group is 3.
Line 2HAVING COUNT(*) > 1 removes the bo and cy groups, each of which holds a single row.
Line 3The same test is impossible in WHERE: one row cannot know how many other rows share its email.
Line 4MIN(id) reports the earliest row of each surviving group, which is what you keep if the next step is deleting the copies.
HAVING with no GROUP BY
With aggregates but no GROUP BY the whole filtered table is one group, and HAVING can delete that single row.
SELECT COUNT(*) AS orders, SUM(amount) AS total
FROM sales
WHERE status = 'paid';
SELECT COUNT(*) AS orders, SUM(amount) AS total
FROM sales
WHERE status = 'paid'
HAVING SUM(amount) > 5000;Example explained
Line 1The first query has no GROUP BY, so the five paid rows form one implicit group and one row comes back.
Line 2The second query is identical except that HAVING tests that one group: 1170 is not above 5000.
Line 3Because the only group fails the test, the result is empty, even though an aggregate query without HAVING always yields exactly one row.
Line 4This is also proof that GROUP BY is not required for HAVING to be legal.
Why an aggregate in WHERE is rejected
Moving the group condition back into WHERE on the same sales table produces an immediate error rather than wrong numbers.
SELECT region, SUM(amount) AS total
FROM sales
WHERE SUM(amount) > 300
GROUP BY region;Example explained
Line 1WHERE is evaluated once per row, before GROUP BY has built anything, so SUM has no set of rows to add up.
Line 2The caret marks the aggregate call itself, which is the token that is illegal in that position.
Line 3The query is refused during analysis; the engine does not quietly treat the clause as a HAVING.
Line 4Deleting the WHERE line and writing HAVING SUM(amount) > 300 after GROUP BY region makes it valid.
Important notes
HAVING can only reference grouping keys and aggregates, so HAVING id > 5 in a query grouped by region is rejected: the group holds many ids, not one.
HAVING is applied before ORDER BY and LIMIT, so LIMIT 3 counts only the groups that HAVING kept, not the first three groups produced by GROUP BY.
Common mistakes
Writing WHERE COUNT(*) > 1 instead of HAVING COUNT(*) > 1: the query is rejected outright, because WHERE runs per row and there is no count to compare yet.
Forcing a row filter into HAVING by wrapping it in an aggregate, such as HAVING MAX(status) = 'paid': the query runs, but the voided rows were never removed, so SUM(amount) is still inflated by them.
Filtering on the SELECT alias with HAVING paid_total >= 320: SQLite and MySQL accept it, while PostgreSQL fails with column "paid_total" does not exist, so the query breaks the moment the project switches engines.
Try it yourself
Change, predict, then run
Recreate the sales table and write one query returning each region's paid order count and paid total, keeping only regions with two or more paid orders. Then move status = 'paid' from WHERE into HAVING and record exactly what changes: the error message, or the totals that are now too large.
Open the SQL workspaceCheck your understanding
A table holds one row per order. You filter to paid orders, group by region, and add HAVING COUNT(*) = 0 to find regions with no paid orders, but the query returns nothing at all. Why?
- GROUP BY only creates a group for rows that survived WHERE, so every group contains at least one row
- COUNT(*) skips NULL values, so it can never evaluate to 0
- HAVING is evaluated before the aggregates are computed, so COUNT(*) is not known yet
- A comparison to 0 must be written in WHERE, which runs before the grouping
Show answer
Groups are built from the surviving rows, so an empty group is never produced and COUNT(*) = 0 can never be true; to find regions with no paid orders you must aggregate over all rows, for example SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) = 0, or LEFT JOIN a list of regions. The NULL option is tempting because COUNT does ignore NULLs, but that applies to COUNT(column); COUNT(*) counts rows, and either way the reason the result is empty is the missing group, not the counting rule.