Module 2: Picking rows and columns · Lesson 2.6 · about 30 min
CASE: if-then inside a query
Use CASE WHEN to label rows, sort them into bands, fix messy values and count things conditionally, with live examples on a store database.
Module 2 · about 30 minutes · read first: 2.4. You’ll be able to write CASE expressions that label rows, group numbers into bands, tidy messy values, and count only the rows you care about.
In everyday terms: a coin-sorting machine. Each coin rolls past a row of slots and drops into the first one it fits; whatever fits no slot rolls into the tray at the end (the ELSE).
1. Why this matters
Business people think in labels, not raw numbers: “small, medium, large orders”, “Ontario vs the rest”, “new vs returning customers”. CASE is how you turn the data’s values into the business’s categories, right inside the query. It also powers one of the most useful analyst tricks: counting several different things in a single pass.
2. The shape of CASE
CASE is an expression: it works out one value per row, so it can go anywhere a column can (SELECT, WHERE, ORDER BY, GROUP BY).
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE fallback
END
-- try
SELECT order_id, item, amount,
CASE
WHEN amount >= 50 THEN 'Large'
WHEN amount >= 20 THEN 'Medium'
ELSE 'Small'
END AS size
FROM mini_orders
Press Show how it runs and stop at the SELECT step: the new size column is worked out row by row. The Duvet ($90) and Speaker ($60) are Large, the Kettles ($45) and Puzzle ($20) are Medium, the Bagels ($5) are Small.
3. First true WHEN wins
CASE reads the WHEN lines from the top and stops at the first one that is true. The Duvet at $90 passes >= 50 and is labelled Large straight away; it never reaches the second test. A Kettle at $45 fails >= 50, moves on, and is caught by >= 20. So the second line really means “from 20 up to (not including) 50”, without you writing the upper limit.
That’s why bands go from one end to the other. Swap the two WHEN lines in the box above and run it: now everything $20 and over is “Medium”, and nothing is ever “Large”, because the broader test catches them first.
- CASE ... END
- An expression that works out one value per row.
- WHEN test THEN value
- If the test is true for this row, use this value and stop.
- ELSE value
- The value for rows that passed no test. Without ELSE they get NULL.
- Band
- A range of numbers given one label, such as "Medium: 20 to under 50".
- SUM(CASE WHEN test THEN 1 ELSE 0 END)
- Counts the rows that pass the test.
- Physical meaning
- CASE adds a column without changing the number of rows. Each row goes down the WHEN list on its own and stops at the first true test, so the order of the WHEN lines decides the label.
“All the WHEN tests are checked, and the row gets the one that fits best.” CASE stops at the first true WHEN. Put WHEN amount >= 20 above WHEN amount >= 50 and every $90 order becomes Medium. Write bands from one end to the other, and always add an ELSE so surprises show up.
Watch the size label being worked out, one row at a time (the live box’s Show how it runs button animates the real query):
| item | amount | size |
|---|---|---|
| Kettle | 45 | Medium |
| Bagels | 5 | Small |
| Puzzle | 20 | Medium |
| Duvet | 90 | Large |
| Kettle | 45 | Medium |
| Speaker | 60 | Large |
4. ELSE, and what happens without it
Rows that match no WHEN get the ELSE value. Without an ELSE they get NULL:
-- try
SELECT product_name, unit_price,
CASE
WHEN unit_price < 5 THEN 'Under $5'
WHEN unit_price < 20 THEN '$5 to $20'
END AS band
FROM products
ORDER BY unit_price DESC
LIMIT 8
The expensive products show a blank band. Add ELSE '$20 and up' before END and run it again. A good habit: always write an ELSE, even if it’s ELSE 'Check me', so surprises show up instead of hiding as NULL.
5. Labelling and tidying
CASE is handy for turning codes into words and NULLs into something readable:
-- try
SELECT store_name,
CASE
WHEN city IS NULL THEN 'Online'
WHEN province = 'ON' THEN 'Ontario'
ELSE 'Outside Ontario'
END AS area
FROM stores
Note the test city IS NULL comes first. If it came after province = 'ON', it would still work here, but putting the NULL test first is a safe habit, since a NULL fails every other comparison.
There is also a short form when you compare one column with fixed values:
-- try
SELECT customer_id, loyalty_tier,
CASE loyalty_tier
WHEN 'Gold' THEN 3
WHEN 'Silver' THEN 2
WHEN 'Bronze' THEN 1
ELSE 0
END AS tier_rank
FROM customers
LIMIT 10
6. CASE in ORDER BY
Sorting by a CASE puts rows in a business order instead of alphabetical order. Here Gold members come first, then Silver, then Bronze, then no tier:
-- try
SELECT first_name, last_name, loyalty_tier
FROM customers
WHERE customer_id <= 15
ORDER BY CASE loyalty_tier WHEN 'Gold' THEN 1 WHEN 'Silver' THEN 2 WHEN 'Bronze' THEN 3 ELSE 4 END,
last_name
7. Counting with CASE
Put a CASE that gives 1 or 0 inside SUM and you count the rows that pass the test. Several of these side by side count several things in one pass. (SUM gets its own lesson next module; here, just read it as “add up”.)
-- try
SELECT SUM(CASE WHEN channel = 'online' THEN 1 ELSE 0 END) AS online_orders,
SUM(CASE WHEN channel = 'store' THEN 1 ELSE 0 END) AS store_orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
WHERE order_date LIKE '2025%'
This conditional aggregation pattern returns in Lesson 8.6 for turning rows into columns. SQL Server also has IIF(test, a, b), and PostgreSQL has COUNT(*) FILTER (WHERE test); CASE works in all of them.
Maths and principle
Principle: CASE defines a piecewise function of each row, exactly like the piecewise functions in maths class. Because it looks at one row at a time, it adds a column without changing the number of rows.
Equation: size(r) = ‘Large’ if r.amount ≥ 50; ‘Medium’ if 20 ≤ r.amount < 50; ‘Small’ otherwise. The “first true WHEN wins” rule is what makes the second piece mean 20 ≤ amount < 50, without writing the < 50. For counting, the CASE is an indicator function: 1p(r) = 1 if p(r) is true, else 0, and Σ 1p(r) = |σp(T)|.
Analogy: a sorting machine for parcels with a row of gates. Each parcel goes through the first gate that fits it. It breaks down a little with NULL: a NULL fails every gate, so it always ends up at ELSE.
Try it: with bands ”< 5”, ”< 20”, ELSE, which band does a $5.00 product fall into? (‘$5 to $20’: 5 < 5 is false, 5 < 20 is true.)
8. Worked example
Question: how are Northbay’s 61 products spread across price bands: under $5, $5 to $20, and $20 and up?
- Band each product with CASE:
< 5, then< 20, then ELSE. - Count the products per band. That needs GROUP BY, which you’ll meet in Lesson 3.2, so here we use the counting trick from section 7 instead:
-- try
SELECT SUM(CASE WHEN unit_price < 5 THEN 1 ELSE 0 END) AS under_5,
SUM(CASE WHEN unit_price >= 5 AND unit_price < 20 THEN 1 ELSE 0 END) AS from_5_to_20,
SUM(CASE WHEN unit_price >= 20 THEN 1 ELSE 0 END) AS from_20_up
FROM products
- Result: 19 under $5, 25 from $5 to $20, 17 at $20 and up. Check: 19 + 25 + 17 = 61, every product counted once. Always do that check: if the bands overlap or leave a gap, the total won’t match.
9. Practice
-- exercise: Label each employee's salary: 'Senior' for 100000 or more, 'Mid' for 60000 or more, otherwise 'Entry'. Show name, salary and the label (call it level).
-- hint: Start with the highest band: WHEN salary >= 100000 THEN 'Senior' WHEN salary >= 60000 THEN 'Mid' ELSE 'Entry'.
SELECT name, salary
FROM employees
-- answer:
SELECT name, salary,
CASE WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid'
ELSE 'Entry' END AS level
FROM employees
-- exercise: This query should put each mini order into 'Large' (50 or more) or 'Medium' (20 or more), else 'Small', but nothing comes out Large. Fix it.
-- hint: The first true WHEN wins, so the broad test is catching everything. Swap the WHEN lines.
SELECT order_id, amount,
CASE WHEN amount >= 20 THEN 'Medium'
WHEN amount >= 50 THEN 'Large'
ELSE 'Small' END AS size
FROM mini_orders
-- answer:
SELECT order_id, amount,
CASE WHEN amount >= 50 THEN 'Large'
WHEN amount >= 20 THEN 'Medium'
ELSE 'Small' END AS size
FROM mini_orders
-- exercise: Count in one row how many customers have a NULL email and how many have a NULL loyalty tier.
-- hint: SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END), and the same for loyalty_tier.
SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS no_email
FROM customers
-- answer:
SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS no_email,
SUM(CASE WHEN loyalty_tier IS NULL THEN 1 ELSE 0 END) AS no_tier
FROM customers
- CASE works out one value per row and can go anywhere a column can.
- WHEN lines are checked from the top; the first true one wins.
- Rows that match no WHEN get ELSE, or NULL if there is no ELSE.
- Put a NULL test first, since NULL fails every comparison.
- SUM(CASE WHEN test THEN 1 ELSE 0 END) counts several things in one pass; check the bands add up to the total.
New words: CASE expression, band, piecewise function, indicator function, conditional aggregation.
Check yourself
Sources
- SQLite documentation, SQL Language Expressions: The CASE expression https://www.sqlite.org/lang_expr.html#the_case_expression
- Microsoft Learn, CASE (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql
- PostgreSQL documentation, Conditional Expressions: CASE https://www.postgresql.org/docs/current/functions-conditional.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.