Every query we've written so far has been honest about what it can do. It retrieves columns, filters rows, sorts results. It reports what's already there.
Today we taught SQL to make decisions.
Not retrieve a price - classify it. Not return a stock level - evaluate it. Not show an age - categorise it. CASE WHEN is the if/elif/else of SQL, and once it clicks, students stop thinking of queries as read-only reporters and start thinking of them as tools that can reason about data.
We worked across two datasets today: Mama Nia's duka for the fundamentals, and Afya Clinic's patient records for the session closer - where CASE WHEN does something genuinely surprising.
The Syntax: If/Elif/Else, SQL Style
Before any code, I wrote the structure on the board:
SELECT column,
CASE
WHEN condition1 THEN 'label1'
WHEN condition2 THEN 'label2'
ELSE 'default_label'
END AS new_column_name
FROM table_name;
"Read it like English. When this is true, give me this label. When that is true, give me that label. Otherwise, give me this default. End the block, name the result."
The ELSE is optional - if you leave it out and no condition matches, PostgreSQL returns NULL. I told the class to always include it unless they explicitly want NULLs. An unexpected NULL in production is harder to debug than a clear default label.
Example 1: Price Tiers - Mama Nia's Duka
The first question I wrote on the board: "Can we look at a price and automatically label it Budget, Mid-range, or Premium - without hardcoding it in Excel?"
Sabuni ya kufulia at KES 55 - Budget. Sukari at KES 165 - Mid-range. Mchele Pishori at KES 235 - Premium. The price_tier column didn't exist in the table. We created it on the fly, from a rule.
I made a point of the ORDER BY price at the end. The results sort by the original numeric price, not by the label alphabetically. You can see the clean transition: Budget rows at the top, then Mid-range, then Premium - which is only possible because we're sorting on the real number, not the string we created.
The question that always comes next: "Can we filter by the new column? Like WHERE price_tier = 'Budget'?"
Honest answer: no - not directly. The alias price_tier doesn't exist when WHERE runs (same reason HAVING aliases don't work). You'd wrap it in a subquery or CTE. We noted that and moved on - it's a Day 2 concept.
Example 2: Stock Status - Real Decisions for Real Operations
Same idea, different business question. Mama Nia needs to know at a glance which products need attention:
Mkate and Mtindi at stock level 20 - Low Stock. Maziwa Fresh at 30 - Adequate. Mchele Pishori at 60 and Sukari at 90 - Well Stocked.
I pointed at the boundary between Low Stock and Adequate - Mkate and Mtindi both at 20, both flagged Low Stock. Then Maziwa Fresh at exactly 30 - Adequate. "What did we say about BETWEEN last week? Inclusive on both ends. stock_level < 30 doesn't include 30. BETWEEN 30 AND 50 picks it up. No product falls through the gap - but only because we thought the boundaries through carefully."
This is the practical skill: before writing a CASE WHEN, map out the boundaries on paper first. Overlapping conditions, gaps in coverage, and fence-post errors are all invisible until a real product shows up in the wrong category.
Example 3: The Simple CASE WHEN - Exact Value Matching
The first two examples used the searched form - conditions are full expressions. There's also a simple form for when you're matching one column against a list of exact values:
Chai ya Majani and Soda - Beverages, Aisle 3. Mtindi and Maziwa Fresh - Dairy, Aisle 2. All five Grains & Cereals products - Aisle 1. Sabuni ya kufulia - Household, Aisle 4. Mkate - Snacks & Bakery, Aisle 5.
The difference from the searched form: CASE product_category WHEN 'Dairy' THEN ... - the column goes after CASE, not after WHEN. Cleaner when you're doing one-to-one label mapping. Less flexible - you can't write range conditions in this form.
I told the class: "Think of the simple form as a lookup table built into your query. You're saying: for this column, when the value is exactly this, map it to that. It's exact matches only - no greater-than, no BETWEEN."
One student - Njeri - immediately asked: "What if a product is in a category that isn't listed? Like a new category we haven't assigned an aisle to yet?"
She ran it. NULL in the aisle column for any unmatched category. Which is why you add an ELSE:
CASE product_category
WHEN 'Grains & Cereals' THEN 'Aisle 1'
...
ELSE 'Aisle TBC'
END AS aisle
Njeri caught the production bug before the query ever went anywhere near production. That question was worth the whole example.
Switching Datasets: Afya Clinic Patients
Same concept, completely different context - clinic.afya_patients. A table of patients with names and ages. The question: can we classify every patient by age bracket automatically?
Faith Nyambura, age 5 - Child. Amina Hassan, age 8 - Child. Ruth Chebet, age 16 - Teen. James Otieno, age 34 - Adult. Grace Wanjiru, age 67 - Senior. John Mutua, age 81 - Senior.
The same CASE WHEN structure, the same four-condition logic, a completely different domain. I made this point explicitly: "CASE WHEN is a pattern. Once you understand the pattern, it works on prices, stock levels, ages, credit scores, risk categories, exam grades - anything where you need to classify a number or a value into a label."
The Session Closer: SUM(CASE WHEN) - A Pivot in Eight Lines
This is the part of the session I look forward to most. It's the moment CASE WHEN stops being a labelling tool and becomes an analytical one.
The question: "How many patients fall into each age bracket - all in a single row?"
Without this technique, you'd write four separate COUNT queries. Or use GROUP BY, which gives you four rows. But what if you need all four numbers side by side - in one row - for a report or a dashboard?
I drew this on the board first, by hand:
Name age (age < 12)
Faith 5 1
Amina 8 1
Ruth 16 0
Mary 29 0
James 34 0
Linda 38 0
Peter 45 0
Samuel 55 0
Grace 67 0
David 72 0
John 81 0
= 2
"If age < 12 is true, we get a 1. If it's false, we get a 0. SUM() adds up all the 1s. That's the child count. We do the same thing for every bracket - four CASE WHEN expressions inside four SUM() calls - all in the same SELECT."
child_count | teen_count | adult_count | senior_count
------------|------------|-------------|-------------
2 | 1 | 5 | 3
One row. Four columns. Every patient counted, every bracket covered, zero GROUP BY.
The silence in the room after that ran was a good kind. A few people read the output twice.
Kamau said: "So you turned a CASE WHEN into a number you can add. A condition became arithmetic."
Exactly. That's the whole trick. THEN 1 ELSE 0 converts a boolean evaluation into a number. SUM() adds the numbers. The result is a count of rows where the condition was true - without ever writing COUNT(*) or GROUP BY.
This pattern has a name in data engineering: a pivot. You're taking categories that would normally appear as rows (one row per bracket) and spreading them across columns (one column per bracket) in a single row. It shows up constantly in reporting - monthly revenue per product line, weekly signups by region, patient counts by age group for a clinic dashboard.
Two Forms, One Concept
Before closing I put the comparison on the board:
| Form | Syntax | Use when |
|---|---|---|
| Searched | CASE WHEN condition THEN ... |
Ranges, multiple columns, complex logic |
| Simple | CASE column WHEN value THEN ... |
Exact value mapping on one column |
Both produce a new computed column. Both end with END AS column_name. The searched form is more powerful - you can mix conditions, columns, BETWEEN, anything. The simple form is cleaner when you're doing pure lookup mapping.
Practice Problems
Easy:
-- 1. Add a 'restock_urgency' column: stock < 20 = 'URGENT', 20–40 = 'Soon', else = 'OK'
-- 2. Label each product as 'Affordable' (price < 80) or 'Pricey' (price >= 80)
-- 3. Using afya_patients, label patients as 'Minor' (age < 18) or 'Adult' (age >= 18)
Medium:
-- Map each supplier to a region using simple CASE WHEN:
-- 'Kenya Grain Millers' → 'Central'
-- 'Brookside Dairy' → 'Rift Valley'
-- anything else → 'Unknown'
SELECT product_name, supplier,
CASE supplier
WHEN 'Kenya Grain Millers' THEN 'Central'
WHEN 'Brookside Dairy' THEN 'Rift Valley'
ELSE 'Unknown'
END AS supplier_region
FROM duka.duka_products;
Challenge:
-- Using duka_products, build a one-row stock health summary:
-- How many products are Low Stock? Adequate? Well Stocked?
-- Use SUM(CASE WHEN) - same pattern as the afya_patients pivot
-- Thresholds: Low Stock < 30, Adequate 30–50, Well Stocked > 50
-- Your query here...
What I Noticed Teaching This Session
1. The hand-drawn 1s and 0s table unlocked SUM(CASE WHEN). Writing out Faith (1), Amina (1), Ruth (0)... David (0), John (0), total = 2 - by hand, slowly - before showing any SQL, meant that when the query ran and returned 2, students understood exactly where that number came from. The concept was clear before the syntax appeared.
2. Switching datasets mid-session helped. Using Mama Nia's duka for the first three examples and Afya Clinic for the last two showed that CASE WHEN is a pattern, not a Duka-specific trick. The concept transferred immediately because the structure was identical - only the domain changed.
What's Next: JOINs
CASE WHEN works on one table. Next session we bring two tables together for the first time.
-- A preview
SELECT p.product_name, p.price, s.contact_person,
CASE
WHEN p.stock_level < 30 THEN 'Call supplier now'
ELSE 'Stock OK'
END AS restock_action
FROM duka_products p
JOIN suppliers s ON p.supplier_id = s.id;
CASE WHEN inside a JOIN. The two concepts stack - everything we learned today works inside the queries we'll write next week.
I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
I write weekly about what we covered, what worked, and what surprised me.
Top comments (0)