Chapter 08 · Aggregation and Grouping
Conditional Aggregation
Conditional aggregation turns business rules into selective contributions to COUNT, SUM, and AVG without multiplying scans or joins.
Learning outcomes
Conditional aggregation makes only selected rows contribute to each metric. It is the foundation of status dashboards, cohort counts, pivot-style reports, and ratios computed from one grouped input.
Use CASE inside SUM, COUNT, and AVG to define metric-specific inputs.
Use FILTER (WHERE ...) where supported and understand its equivalence.
Produce several category metrics in one grouped query.
Protect ratios from integer division, NULLs, and zero denominators.
The selective-contribution pattern
SELECT channel, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_sales, SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_sales, SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_salesFROM saleGROUP BY channelORDER BY channel;Each CASE returns one for rows belonging to its metric and zero otherwise. SUM combines those contributions within the channel group.
COUNT with CASE
SELECT channel, COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_sales, COUNT(CASE WHEN discount_pct IS NOT NULL THEN 1 END) AS discounted_salesFROM saleGROUP BY channelORDER BY channel;Omitting ELSE makes CASE return NULL for nonmatching rows, and COUNT(expression) ignores those NULLs.
FILTER syntax
SELECT channel, COUNT(*) FILTER (WHERE status = 'paid') AS paid_sales, SUM(quantity) FILTER (WHERE status = 'paid') AS paid_units, AVG(discount_pct) FILTER (WHERE discount_pct IS NOT NULL) AS average_known_discountFROM saleGROUP BY channelORDER BY channel;FILTER reads naturally and keeps the aggregate argument simple. SQL Server and MySQL commonly use CASE-based equivalents instead.
Conditional sums of business values
SELECT s.channel, ROUND(SUM(CASE WHEN s.status = 'paid' THEN p.unit_price * s.quantity ELSE 0 END), 2) AS paid_gross, ROUND(SUM(CASE WHEN s.status = 'pending' THEN p.unit_price * s.quantity ELSE 0 END), 2) AS pending_gross, ROUND(SUM(CASE WHEN s.status = 'refunded' THEN p.unit_price * s.quantity ELSE 0 END), 2) AS refunded_grossFROM sale AS sJOIN product AS p ON p.product_id = s.product_idGROUP BY s.channelORDER BY s.channel;Pivot-style reporting
Conditional aggregates turn category values into separate metric columns. This is a fixed, query-defined pivot rather than a dynamic pivot operation.
Ratios need a safe denominator
SELECT channel, COUNT(*) AS all_sales, SUM(status = 'paid') AS paid_sales, ROUND( 100.0 * SUM(status = 'paid') / NULLIF(COUNT(*), 0), 1 ) AS paid_sale_pctFROM saleGROUP BY channelORDER BY channel;100.0 prevents integer arithmetic, and NULLIF(..., 0) prevents division by zero when the pattern is reused after an outer join or over generated categories.
Conditional averages
SELECT channel, AVG(CASE WHEN status = 'paid' THEN quantity END) AS average_paid_quantityFROM saleGROUP BY channelORDER BY channel;Nonmatching rows contribute NULL and are excluded from AVG. Writing ELSE 0 would include them as zero-valued observations and change the denominator.
Retain categories with no matching facts
WITH expected_status(status) AS ( VALUES ('paid'), ('pending'), ('refunded'), ('cancelled'))SELECT e.status, COUNT(s.sale_id) AS sale_count, COALESCE(SUM(s.quantity), 0) AS units_soldFROM expected_status AS eLEFT JOIN sale AS s ON s.status = e.statusGROUP BY e.statusORDER BY e.status;The category source supplies the missing cancelled row. Counting the child key and defaulting SUM to zero preserves correct zero metrics.
CASE versus multiple subqueries
One grouped input
Several metrics can be calculated from the same row stream.
Aligned grain
All metrics share the same grouping keys and cannot drift into mismatched result sets.
Visible rules
Each metric predicate is readable beside its aggregate.
Fixed categories
Adding a new pivot column still requires changing the SELECT list.
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
- Count paid, pending, and refunded sales by channel.
- Calculate paid and pending units in the same query.
- Return paid gross value and paid-sale percentage.
- Compare CASE and FILTER versions.
- Retain an expected category that has no matching rows.
SELECT channel, COUNT(*) AS all_sales, COUNT(*) FILTER (WHERE status = 'paid') AS paid_sales, SUM(quantity) FILTER (WHERE status = 'paid') AS paid_units, SUM(quantity) FILTER (WHERE status = 'pending') AS pending_units, ROUND( 100.0 * COUNT(*) FILTER (WHERE status = 'paid') / NULLIF(COUNT(*), 0), 1 ) AS paid_pctFROM saleGROUP BY channelORDER BY channel;Checkpoint
Design the metric inputs
- Why does SUM(CASE ... THEN 1 ELSE 0 END) count matching rows?
- What happens when COUNT(CASE ...) receives NULL from a nonmatching row?
- Why should AVG(CASE ...) usually omit ELSE 0?
- Which engines commonly support FILTER?
- How do you retain a category with no fact rows?
Review the answers
Each match contributes one to SUM. COUNT ignores NULL. ELSE 0 would alter the average denominator. SQLite and PostgreSQL support FILTER; CASE is more universal. Start from an expected-category table or CTE and LEFT JOIN the facts.
Summary and references
- CASE controls which value each row contributes to an aggregate.
- FILTER expresses an aggregate-specific WHERE condition.
- Conditional aggregation creates several aligned metrics in one grouped query.
- Ratios need fractional arithmetic and safe denominators.
- Expected-category sources preserve zero-count categories.