HAVING versus WHERE

WHERE filters rows before grouping. HAVING filters groups after. They look similar, they are not interchangeable, and choosing the wrong one gives the wrong answer rather than an error.

The two, side by side

SELECT shelf, count(*) AS titles
FROM books
GROUP BY shelf
HAVING count(*) > 2
ORDER BY shelf;

 shelf | titles
-------+--------
 C-01  |      3
 F-01  |      3
 F-05  |      3
 F-08  |      3
(4 rows)

Four shelves hold more than two titles. HAVING could not be WHERE here, because count(*) does not exist until the piles do.

Now with a WHERE as well:

SELECT shelf, count(*) AS titles
FROM books
WHERE copies > 1
GROUP BY shelf
HAVING count(*) > 2
ORDER BY shelf;

 shelf | titles
-------+--------
 C-01  |      3
 F-01  |      3
 F-05  |      3
(3 rows)

Four became three. F-08 dropped out — not because it has fewer than three titles, but because one of its three has only one copy, so WHERE removed that row before the pile was made, leaving a pile of two.

That is the whole distinction, in one row of difference:

• WHERE copies > 1 — which books to consider.
• HAVING count(*) > 2 — which shelves to report.

Which to use

Use WHERE whenever you can. It removes rows before the grouping work happens, so there is less to group. HAVING is for conditions that can only be answered once the pile exists.

-- correct, and efficient: filter rows first
WHERE borrowed_on >= DATE '2026-01-01'

-- correct only for an aggregate
HAVING count(*) > 5
HAVING sum(fine_paise) > 10000
HAVING avg(price_paise) < 30000

A condition on a grouping column can legally go in either:

GROUP BY shelf HAVING shelf <> 'F-01'      -- works
WHERE shelf <> 'F-01' GROUP BY shelf       -- same answer, less work

Put it in WHERE. Same result, fewer rows grouped, and an index can help.

What each clause can see

WHERE   ── sees columns. Cannot see aggregates.
HAVING  ── sees aggregates and grouping columns. Cannot see non-grouped columns.

SELECT shelf, count(*) FROM books WHERE count(*) > 2 GROUP BY shelf;
-- ERROR:  aggregate functions are not allowed in WHERE

SELECT shelf, count(*) FROM books GROUP BY shelf HAVING title <> 'The Guide';
-- ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an
--         aggregate function

The second error is the same one from the previous lesson, and for the same reason: after grouping, title does not exist as a single value. If you want "shelves that contain no copy of The Guide", that is an aggregate condition:

HAVING count(*) FILTER (WHERE title = 'The Guide') = 0

FILTER again, doing in one line what would otherwise need a subquery.

HAVING with no GROUP BY

Legal, and occasionally useful:

SELECT count(*) FROM books HAVING count(*) > 100;

With no GROUP BY the whole table is one group, so this returns either one row or none. It is a way of saying "give me this number, but only if it exceeds a threshold".

Rare, and worth recognising so it does not look like a mistake.

Aliases do not work in HAVING

SELECT shelf, count(*) AS titles FROM books GROUP BY shelf HAVING titles > 2;

ERROR:  column "titles" does not exist
HINT:  Perhaps you meant to reference the column "books.title".

Repeat the aggregate:

HAVING count(*) > 2

This is worth being precise about, because the four clauses differ and the pattern is not intuitive:

GROUP BY and ORDER BY accept one; WHERE and HAVING do not. There is no performance cost to repeating the aggregate — the database computes it once regardless.

The pattern you will write most

"Things that have more than N of something" is an extremely common report, and this is its shape:

SELECT m.id, m.name, count(*) AS loans, sum(l.fine_paise) AS fines
FROM members m
JOIN loans l ON l.member_id = m.id
WHERE l.borrowed_on >= DATE '2026-01-01'      -- which loans count
GROUP BY m.id, m.name                          -- one row per member
HAVING count(*) > 5                            -- only the frequent borrowers
ORDER BY loans DESC
LIMIT 10;

Every clause doing its own job, in order: which rows, which piles, which piles to keep, how to sort, how many. Module 5 covers the join; the shape is worth recognising now.

Note GROUP BY m.id, m.name — group by the key, from the previous lesson.

A trap worth naming

-- "members who have never been fined"
SELECT m.name FROM members m
JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name
HAVING sum(l.fine_paise) = 0;

This misses two groups of people, and both misses are silent.

Members with no loans at all never appear, because an inner join drops them — they have no rows to group. Members whose fines are all NULL give sum(...) = NULL, and NULL = 0 is UNKNOWN, not true, so they are dropped too.

HAVING COALESCE(sum(l.fine_paise), 0) = 0

fixes the second. The first needs a LEFT JOIN, which is module 5 — and it is the single most common reason a "who has never done X" report is wrong.

Whenever you write HAVING, ask what is not in the result and whether it should be.

Check your work

The distinction. WHERE chooses rows before grouping; HAVING chooses groups after.

What the one-row difference showed. F-08 dropped because WHERE removed one of its books before the pile was made, not because it had too few titles.

Which to prefer, and why. WHERE — fewer rows to group, and an index can help.

Where a condition on a grouping column should go. WHERE.

What WHERE cannot see. Aggregates.

What HAVING cannot see. Non-grouped columns.

How to express "groups containing none of X". count(*) FILTER (WHERE ...) = 0.

What HAVING without GROUP BY returns. One row or none.

Whether HAVING can use a SELECT alias. No — column "titles" does not exist. GROUP BY and ORDER BY can; WHERE and HAVING cannot.

The two silent misses in a "never been fined" query. Members with no loans, and members whose sums are NULL.

The question to ask of every HAVING. What is missing from this result?

Practice

1. Find shelves with more than two titles.
2. Add WHERE copies > 1 and explain which shelf disappeared and why.
3. Try the same condition in WHERE instead of HAVING and read the error.
4. Try filtering on title in HAVING and read that error.
5. Move a condition on shelf from HAVING to WHERE. Confirm the answer is identical.
6. Find shelves that contain no copy of a particular title, using FILTER.
7. Write a HAVING with no GROUP BY that returns one row, then change the threshold so it returns none.
8. Use a SELECT alias in HAVING and read the error. Then try the same alias in ORDER BY and in GROUP BY, and note which of the four clauses accept one.
9. Find members who have borrowed more than five books, with their total fines.
10. Every member in the seed data has borrowed something, so insert one who has not: INSERT INTO members (name, joined) VALUES ('Nobody Here', current_date); Now run the "never been fined" query and confirm they do not appear.
11. Fix the NULL half with COALESCE. Note that the other half still needs module 5.
12. Find authors with more than two books in the library. You will need a join — try it, and come back after module 5 if it fights you.

Official documentation

• PostgreSQL — GROUP BY and HAVING — Both clauses and where they sit.
• PostgreSQL — SELECT reference — The formal definition, including HAVING without GROUP BY.
• PostgreSQL — Aggregate expressions — FILTER, which makes several HAVING conditions expressible in one line.

Next: counting things correctly, which is harder than it sounds.