Chapter 08 · Aggregation and Grouping
HAVING Versus WHERE
WHERE decides which detail rows may enter a group; HAVING decides which completed groups may leave the aggregation stage.
Learning outcomes
Grouped queries have two distinct filter stages. WHERE removes detail rows before grouping. HAVING evaluates conditions after groups and aggregate values exist.
Place row-level predicates in WHERE and group-level predicates in HAVING.
Explain the logical order of FROM, WHERE, GROUP BY, HAVING, and SELECT.
Predict how moving a predicate changes aggregate inputs.
Write efficient queries that filter early without changing meaning.
Logical processing sequence
This is a logical model, not necessarily the physical execution order. The optimizer may transform the plan while preserving the same semantics.
WHERE filters rows before aggregation
SELECT channel, COUNT(*) AS paid_sale_count, SUM(quantity) AS paid_unitsFROM saleWHERE status = 'paid'GROUP BY channelORDER BY channel;Pending and refunded rows never enter a channel group. Every aggregate is computed only from paid rows.
HAVING filters completed groups
SELECT customer_id, COUNT(*) AS sale_count, SUM(quantity) AS total_unitsFROM saleGROUP BY customer_idHAVING SUM(quantity) >= 4ORDER BY customer_id;All sale rows participate in their customer groups. After total units are calculated, groups below four units are discarded.
Use both stages together
SELECT customer_id, COUNT(*) AS paid_sale_count, SUM(quantity) AS paid_unitsFROM saleWHERE status = 'paid'GROUP BY customer_idHAVING SUM(quantity) >= 3ORDER BY customer_id;The requirement has two clauses: only paid detail rows are eligible, and only resulting customer groups with at least three units are reported.
Moving a predicate changes the question
| Predicate placement | Question answered |
|---|---|
WHERE status = 'paid' | Summarize only paid sales. |
HAVING SUM(status = 'paid') > 0 | Summarize all sales, but keep customers who have at least one paid sale. |
| Both | Summarize paid sales and then apply a group threshold. |
SELECT customer_id, COUNT(*) AS all_sale_count, SUM(quantity) AS all_unitsFROM saleGROUP BY customer_idHAVING SUM(status = 'paid') > 0ORDER BY customer_id;Do not put aggregate conditions in WHERE
SELECT customer_id, SUM(quantity) AS total_unitsFROM saleWHERE SUM(quantity) >= 4GROUP BY customer_id;The aggregate value does not exist until after grouping. The correct predicate belongs in HAVING.
Alias use in HAVING varies
SELECT customer_id, SUM(quantity) AS total_unitsFROM saleGROUP BY customer_idHAVING SUM(quantity) >= 4;SELECT customer_id, SUM(quantity) AS total_unitsFROM saleGROUP BY customer_idHAVING total_units >= 4;SQLite and several other systems accept the alias, but repeating the aggregate expression is safer in portable teaching material.
Push nonaggregate predicates early
A condition that depends only on detail-row columns usually belongs in WHERE. Filtering before grouping reduces the rows that must be joined and aggregated.
SELECT channel, COUNT(*) AS sale_count, SUM(quantity) AS units_soldFROM saleWHERE sold_at >= '2026-08-03' AND status <> 'refunded'GROUP BY channelHAVING COUNT(*) >= 2ORDER BY channel;HAVING without GROUP BY
SELECT SUM(quantity) AS total_unitsFROM saleHAVING SUM(quantity) >= 10;An aggregate query without GROUP BY has one group containing the entire filtered input. HAVING can keep or discard that single summary row, though support and style conventions vary across engines.
Practice database
Run this setup once in a disposable SQLite database. Every Chapter 8 example uses the same customer, product, and sale facts so changes in result grain remain easy to compare.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), discount_pct REAL, sold_at TEXT NOT NULL, channel TEXT NOT NULL CHECK (channel IN ('web', 'partner', 'direct')), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), CHECK (discount_pct IS NULL OR discount_pct BETWEEN 0 AND 100)) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer'), (2, 'Omar Haddad', 'Berlin', 'business'), (3, 'Lina Chen', NULL, 'consumer'), (4, 'Ava Morgan', 'Berlin', 'consumer'), (5, 'Noah Silva', 'Lisbon', 'business'), (6, 'Marta Costa', NULL, 'business');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, NULL, '2026-08-01 09:15:00', 'web', 'paid'), (101, 2, 11, 3, 10, '2026-08-01 10:45:00', 'direct', 'paid'), (102, 1, 12, 2, 5, '2026-08-02 11:30:00', 'web', 'paid'), (103, 4, 10, 1, NULL, '2026-08-03 13:05:00', 'partner', 'pending'), (104, 2, 13, 2, 15, '2026-08-03 15:20:00', 'direct', 'paid'), (105, 5, 15, 1, NULL, '2026-08-04 16:40:00', 'partner', 'paid'), (106, 3, 14, 4, 20, '2026-08-05 08:10:00', 'web', 'refunded'), (107, 5, 12, 5, NULL, '2026-08-05 12:00:00', 'web', 'paid'), (108, 2, 15, 1, 10, '2026-08-06 09:25:00', 'direct', 'pending'), (109, 4, 13, 3, NULL, '2026-08-06 14:50:00', 'partner', 'paid');The data includes repeated cities, customers with no sales, nullable discounts, three sales statuses, and multiple channels. Those variations make aggregate behavior visible.
Practice lab
- Summarize paid units by channel.
- Keep channels with at least two paid sales.
- Find customers whose all-status activity exceeds five units but who have at least one paid sale.
- Explain why filtering refunded rows in WHERE is not equivalent to checking for a paid sale in HAVING.
- Rewrite an alias-dependent HAVING clause portably.
SELECT channel, COUNT(*) AS paid_sale_count, SUM(quantity) AS paid_unitsFROM saleWHERE status = 'paid'GROUP BY channelHAVING COUNT(*) >= 2 AND SUM(quantity) >= 3ORDER BY channel;Checkpoint
Choose the filter stage
- Where should a status filter go when nonpaid rows must not affect totals?
- Where should a minimum total-units condition go?
- Can a WHERE condition reference SUM(quantity)?
- What is the semantic difference between WHERE status = paid and HAVING at least one paid row?
- Why can early filtering improve performance?
Review the answers
Use WHERE to exclude nonpaid detail rows. Use HAVING for the group total. WHERE cannot use an aggregate from a later stage. The first changes aggregate inputs; the second keeps all inputs but tests group composition. Early filtering reduces rows entering joins and aggregation.
Summary and references
- WHERE filters detail rows before grouping.
- HAVING filters groups after aggregate values exist.
- Predicate placement changes the question, not just performance.
- Aggregate conditions cannot be evaluated in WHERE.
- Push true row-level restrictions early.