SQL HAVING vs WHERE: Filter Groups by Totals

You will filter whole groups with HAVING, and know when a filter belongs in WHERE instead.

Why can't WHERE filter on a count?

WHERE looks at one row at a time. When it looks at the Grey heron report from 12 January, it can see the date, the place and the number of birds on that row. It cannot see how many Grey heron reports there are in total, because that number only exists once rows have been grouped and counted.

That is why WHERE COUNT(*) >= 3 fails with an error about misusing an aggregate. The counting has not happened yet when WHERE runs. SQL has a second filter for this job: HAVING, which runs after GROUP BY and looks at whole groups.

A simple way to choose: if the condition is about a single report (this date, this place), use WHERE. If it is about a group's summary (this many reports, this many birds in total), use HAVING.

SELECT species, COUNT(*) AS sightings
FROM sightings
GROUP BY species
HAVING COUNT(*) >= 3;

In what order do the steps run?

You write a query in one order and the database runs it in another. The written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The running order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

Each step only sees what the step before it produced. WHERE sees raw rows. GROUP BY sees the rows WHERE kept. HAVING sees groups with their totals. ORDER BY sees the final rows and can sort by any column in them, including a count.

The Rows view follows exactly this order. Run the demo solution and step through it: FROM shows all 15 reports, WHERE drops the two from 2025, GROUP BY gathers the remaining 13 into five species, HAVING keeps the two groups with three or more, and ORDER BY puts Grey heron first.

FROM: start with every row of the table.
WHERE: drop rows that fail a row-level condition.
GROUP BY: gather the remaining rows into groups.
HAVING: drop groups that fail a group-level condition.
SELECT: work out the output columns, including aggregates.
ORDER BY and LIMIT: sort and trim the final rows.

Bird sightings filtered in order: WHERE drops the two 2025 reports, GROUP BY species counts the rest, and HAVING COUNT(*) >= 3 keeps only Grey heron with 5 and Little egret with 3; Kingfisher falls to 2 — `WHERE` works on single rows before grouping, so the 2025 reports never reach a group. `HAVING` then tests each group's count, which leaves Kingfisher with 2 and only Grey heron and Little egret in the result.

How do WHERE and HAVING work together?

Most real questions need both. "Species reported at least three times this year" has a row condition (this year) and a group condition (at least three). Put each in its own place: the date in WHERE, the count in HAVING.

Where you put a filter changes the answer. The Kingfisher has three reports in the table, but one is from December 2025. Filter the date in WHERE and the 2025 row never reaches the count, so the Kingfisher has two and is dropped. Leave the date out and it sneaks in with three. Always ask which rows should be counted before you decide on the group filter.

If a condition could go in either place, such as species = 'Teal', put it in WHERE. Removing rows early means less work for the grouping step, and other readers expect row filters there.

SELECT species, COUNT(*) AS sightings
FROM sightings
WHERE seen_on >= '2026-01-01'
GROUP BY species
HAVING COUNT(*) >= 3
ORDER BY sightings DESC;

Can HAVING use SUM, AVG and other aggregates?

Any aggregate works in HAVING, and it does not have to be one you selected. HAVING SUM(birds) >= 10 keeps species where at least ten birds were counted in total. Teal passes easily with 22 birds from only two reports, while Grey heron, with five reports of single birds and pairs, does not.

That shows the difference between counting reports and counting birds. COUNT(*) answers how often a species was reported. SUM(birds) answers how many individual birds were seen. Both are reasonable questions; make sure the one you write is the one you were asked.

You can combine group conditions with AND and OR, the same way as in WHERE: HAVING COUNT(*) >= 2 AND AVG(birds) > 2 keeps species seen at least twice in groups larger than two on average.

SELECT species, COUNT(*) AS reports, SUM(birds) AS total_birds
FROM sightings
GROUP BY species
HAVING SUM(birds) >= 10
ORDER BY total_birds DESC;

Can HAVING use a column alias?

SQLite and MySQL let you refer to an alias from the SELECT list in HAVING, so HAVING sightings >= 3 works there. PostgreSQL and SQL Server do not, because in the standard order HAVING runs before SELECT has named anything. They want the full expression, HAVING COUNT(*) >= 3.

Writing the aggregate out is portable and only costs a few characters. ORDER BY is different: it runs after SELECT, so every database accepts ORDER BY sightings.

SELECT species, COUNT(*) AS sightings
FROM sightings
GROUP BY species
HAVING COUNT(*) >= 3
ORDER BY sightings DESC;

What goes wrong with plain columns in HAVING?

HAVING place = 'Marsh hide' looks like it should keep Marsh hide reports. It does something else. place is neither grouped nor summarised, so it has no single value per group. SQLite does not complain: it takes place from one row of the group, which is a bare column, and tests that. Other databases reject the query.

The result depends on which row SQLite happened to pick. In the Break it exercise, Ravi has three Marsh hide reports, but his last report was in Old wood, so his whole group fails the test and he vanishes. Even if he passed, the count would include his trips to other places.

A condition on a plain column almost always belongs in WHERE. In HAVING, use grouped columns and aggregates only. A condition on a grouped column, such as HAVING species <> 'Teal', is legal, but it reads better in WHERE too.

Can you use HAVING without GROUP BY?

Yes, though it is rare. Without GROUP BY the whole table is one group, so HAVING decides whether the single summary row appears at all. SELECT COUNT(*) FROM sightings HAVING COUNT(*) > 100 returns nothing, because there are only 15 reports.

That can be handy in checks and alerts, where an empty result means everything is fine. In everyday reports, you will nearly always see HAVING straight after a GROUP BY.

SELECT COUNT(*) AS reports
FROM sightings
HAVING COUNT(*) > 10;

How do you read an AI-written query with HAVING?

AI tools often reach for HAVING when WHERE would do, or the other way round. Read the query in running order and check that each filter is in the step that can actually see what it tests.

A plain column in HAVING that is not in GROUP BY: move it to WHERE.
An aggregate in WHERE: move it to HAVING.
A date range in HAVING: it filters groups by one arbitrary row's date; it belongs in WHERE.
> versus >= in the group filter: "at least 3" is >= 3, "more than 3" is > 3.
An alias in HAVING: fine in SQLite, an error in PostgreSQL.

Field notes

WHERE looks at one row at a time, so it cannot test a count.
HAVING filters groups after they are formed, so it can use aggregates.
The database runs FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY.
Put row filters like dates in WHERE and group filters like counts in HAVING.

Easy to get wrong

WHERE runs before counting, so it cannot test a count. Group filters go in HAVING.

A row-level condition in HAVING tests one arbitrary row per group, and the count still includes every place.

A date filter decides which rows are counted. It has to run before grouping.

Questions people ask

Is HAVING slower than WHERE?

For the same condition, often yes, because the database has already grouped rows it could have discarded. Put row filters in WHERE so less data reaches the grouping step.

Can I use both WHERE and HAVING in one query?

Yes, and most real reports do. WHERE comes before GROUP BY and HAVING after it.

Does HAVING have to come after GROUP BY?

In the written query, yes. SQLite will report a syntax error if HAVING appears before GROUP BY.

Can HAVING compare two aggregates?

Yes. HAVING MAX(birds) > 2 * AVG(birds) keeps species where one report was much bigger than usual.

Filtering groups