lesson 15 Calculations & data quality · intermediate

Conditional aggregation

Calculate several conditional metrics in one summary.

the idea

Put CASE inside SUM to count or add only matching rows. ELSE 0 prevents nonmatching rows from contributing. This can produce multiple summaries in one scan.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count
FROM orders;

Watch for: COUNT(CASE ... ELSE 0 END) counts zero values too because they are not NULL.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
See paid vs other statuses. Orders mix paid and unpaid statuses. Conditional aggregation can count only the paid ones in one pass.
step-1.sql
sql›
```
SELECT id, status, quantity
FROM orders
ORDER BY id;
```
2
Count only paid rows inside SUM. CASE returns 1 for paid rows and 0 otherwise. SUM adds those values into paid_count.
step-2.sql
sql›
```
SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count
FROM orders;
```