Every dataset speaks in raw values — 23, 'DE', 4.99 — but stakeholders think in labels: 'young adult', 'EU region', 'budget tier'. The translator between those worlds is CASE, SQL's if/else. Master it and you'll also unlock a magic trick: turning rows into columns with a single SUM.
What you'll learn
What
CASE evaluates conditions in order and returns the value of the first one that matches: CASE WHEN amount > 100 THEN 'large' WHEN amount > 50 THEN 'medium' ELSE 'small' END. It's an expression, not a statement — legal anywhere a value is: SELECT, WHERE, GROUP BY, ORDER BY, and inside aggregate functions.
Why
Real analysis constantly needs derived categories (age bands, revenue tiers, pass/fail flags) and business-defined orderings that alphabetical sorting can't produce. And conditional aggregation — SUM(CASE WHEN … THEN … ELSE 0 END) — is the standard SQL way to build pivot-style reports: one row per group, one column per category.
Where it's used
KPI dashboards (bucketed metrics), cohort labeling, data-quality flags, custom sort orders for statuses and priorities, and the wide-format tables every BI tool and spreadsheet export expects.
Where this runs in production
Viewing hours become labels — binge, regular, casual, dormant — via CASE bands feeding retention models and win-back campaigns.
Transaction risk scores map to review lanes (auto-approve, manual review, block) with CASE ladders in the analytics layer.
Market dashboards show bookings by listing type as columns — entire home, private room, shared — built with COUNT(CASE WHEN …) per type.
A security officer routes each traveler through the first lane whose rule matches: crew badge? → crew lane. PreCheck? → fast lane. Otherwise → standard lane. One traveler, one lane, rules checked in posted order — a traveler with BOTH a crew badge and PreCheck goes to the crew lane because it's checked first. CASE routes each row exactly like that: first matching WHEN wins, order matters.
Because evaluation stops at the first match, overlapping conditions resolve by position. CASE WHEN amount > 100 THEN 'large' WHEN amount > 50 THEN 'medium' works because a 150 row hits 'large' first. Reverse the branches and every row over 50 — including 150 — lands in 'medium'; 'large' becomes unreachable dead code. Write bands from most specific (or highest) to least, and review any reordering as a logic change.
SELECT
order_id, amount,
CASE
WHEN amount >= 100 THEN 'large'
WHEN amount >= 50 THEN 'medium'
ELSE 'small'
END AS size_tier
FROM orders;The canonical derived category. Note the tiers are collectively exhaustive (ELSE catches the rest) and the boundaries use >= consistently — off-by-one band edges (is exactly 100 large or medium?) are the most common review comment on CASE ladders. Alias the whole expression; the END is part of it.
Omit ELSE and every unmatched row gets NULL — sometimes intended, often a silent hole in the report. Separately: a WHEN whose condition evaluates to unknown (any comparison against NULL) simply doesn't match, falling through to later branches or the ELSE. So CASE WHEN amount > 100 … classifies a NULL amount into the ELSE bucket, not an error. If NULL deserves its own label, test it explicitly — WHEN amount IS NULL THEN 'unknown' — and put that branch FIRST.
Alphabetical order rarely matches business order — 'cancelled' < 'pending' < 'shipped' alphabetically, but ops wants urgent statuses first. ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END builds the custom key inline. You met this trick in ORDER BY & LIMIT's priority example; now you own the machinery.
SELECT
country,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)
AS shipped_revenue
FROM orders
GROUP BY country;Conditional aggregation — the pivot trick. Each CASE emits 1 (or an amount) for rows matching its category and 0 otherwise; SUM adds them up per group. Result: one row per country, one COLUMN per status — the wide format dashboards want. COUNT(CASE WHEN … THEN 1 END) is the equivalent idiom (COUNT ignores the NULL from the missing ELSE). PostgreSQL offers FILTER (WHERE …) as cleaner sugar; the CASE form runs everywhere.
A row with amount = 72 enters the tier ladder. Click each node — first true condition wins, evaluation stops, order is everything.
Mark each order as express-eligible (amount under 500 and status shipped) or not.
One WHEN with a compound condition, one ELSE — the simplest useful ladder. The condition language is identical to WHERE's; anything filterable is CASE-able. Every row gets exactly one of the two labels.
order_id | amount | status | express_flag
----------+--------+-----------+---------------
1001 | 250.00 | shipped | eligible
1002 | 750.00 | shipped | not eligible
1003 | 99.00 | cancelled | not eligible
(3 rows)Your task
Build a one-row-per-plan revenue pivot from subscriptions(sub_id, plan, status, mrr): for each plan, count active subs (active_subs), count churned subs (churned_subs), and sum mrr for ACTIVE subs only (active_mrr). Order by active_mrr descending. Fill in the blanks.
plan | active_subs | churned_subs | active_mrr ------+-------------+--------------+------------ pro | 2 | 1 | 98 base | 2 | 1 | 18 (2 rows)
Write your solution in the editor on the right, then hit Run.
In a CASE with several WHEN branches, which branch determines the result?
A CASE expression has no ELSE and a row matches no WHEN. What does the expression return for that row?
What does SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) compute?
The simple form CASE status WHEN NULL THEN 'missing' ELSE 'present' END is evaluated on a row where status IS NULL. What is returned?
What is wrong?
From orders(order_id, country, amount), produce a one-row summary with: total order count (total_orders), count of orders >= 200 (large_orders), revenue from DE (de_revenue), revenue from everywhere else (other_revenue). Use conditional aggregation — no WHERE, no GROUP BY, one scan.
How would you pivot a long-format table (one row per country-status) into a wide report (one row per country, one column per status) in standard SQL?
Conditional aggregation: GROUP BY country, and for each desired column write SUM(CASE WHEN status = '<value>' THEN 1 ELSE 0 END) — or THEN amount for a revenue pivot. Each CASE acts as a per-row indicator; the aggregate collapses it per group, producing one column per category in a single scan. Compared with the alternative — one filtered subquery per status joined back on country — it's shorter, cheaper (one pass instead of N), and immune to join-key mismatches. Limitations worth stating: the category list is hard-coded, so a new status means editing the query — truly dynamic pivots need engine-specific features (crosstab in PostgreSQL, PIVOT in SQL Server/Snowflake) or the reporting layer. And COUNT(DISTINCT CASE WHEN … THEN user_id END) extends the pattern to unique-entity pivots, exploiting the fact that the missing ELSE yields NULLs, which COUNT DISTINCT ignores.
What are the pitfalls of CASE expressions with NULL data?
Three distinct traps. First, a WHEN condition that compares against a NULL value evaluates to unknown and simply doesn't match, so NULL rows fall through to later branches or the ELSE — a NULL amount lands in whatever the ELSE says, silently miscategorized; the fix is an explicit WHEN x IS NULL branch placed FIRST. Second, the simple form CASE x WHEN NULL uses equality under the hood, and x = NULL is never true — so that branch is dead on arrival; NULL tests require the searched form. Third, a missing ELSE returns NULL for unmatched rows, which is either a deliberate tool (feeding COUNT DISTINCT, which skips NULLs) or a silent hole in a label column, depending on whether you meant it. My review habit: every CASE over a nullable column either handles NULL in its first branch or documents why fall-through is intended.
You need per-segment metrics — counts, conditional revenue, and a rate — for a dashboard. Compare computing them with multiple filtered queries versus one conditional-aggregation query.
Multiple queries — one per metric with different WHERE clauses — are simple to write but scan the table repeatedly, multiply latency and warehouse cost, and worst of all can drift: if someone updates the date filter in three of the four queries, the dashboard quietly reports metrics over inconsistent populations. One conditional-aggregation query computes everything in a single scan with a single set of filters: COUNT(*) for volume, SUM(CASE …) for each conditional count or revenue slice, and ratios as divisions of those aggregates with NULLIF guarding zero denominators. Consistency is structural — every metric sees exactly the same rows. The trade-offs: the single query is denser to review, and if the metrics genuinely need different grains (order-level vs customer-level), forcing them into one scan causes the fan-out bugs from the joins module — in that case I'd use one CTE per grain within one statement, which keeps single-definition filters while respecting grains.
Common Mistakes to Avoid
1) Misordered overlapping branches — testing > 50 before > 100 makes the 'large' tier unreachable; order bands most-restrictive first. 2) Forgetting that a missing ELSE yields NULL — intended for COUNT(CASE …) tricks, a silent hole in label columns otherwise. 3) CASE x WHEN NULL — equality never matches NULL; use the searched form with IS NULL, placed first. 4) Integer division in rates — SUM(CASE WHEN … THEN 1 ELSE 0 END) / COUNT(*) truncates to 0; multiply by 100.0 or cast. 5) Repeating band boundaries inconsistently across queries — extract shared tier definitions into a CTE (or reference table) so 'large' means the same thing on every dashboard.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: CASE with an airport-security-lanes analogy of your own.' • 'Quiz me: CASE ladders over tricky values (NULLs, boundaries) — I predict each row's label.' • 'Show me the dead-branch bug from misordered bands and how to spot it in review.' • 'Walk me from a long GROUP BY result to a wide pivot with SUM(CASE …), column by column.' • 'Interview mode: one-pass funnel metrics with conditional aggregation, then critique my NULL handling.'
Glossary
CASE expression — SQL's inline if/else; returns the first matching branch's value. Searched CASE — WHEN <condition> form; full predicate power. Simple CASE — CASE x WHEN value form; equality only, never matches NULL. Branch (WHEN/THEN) — one condition-result pair; tested in order. ELSE — the default branch; omitted = NULL. Short-circuit — evaluation stops at the first true condition. Dead branch — a WHEN no row can reach due to an earlier broader branch. Derived category — a label column computed from raw values (tiers, bands, flags). Conditional aggregation — aggregates over CASE indicators: SUM(CASE WHEN … THEN 1 ELSE 0 END). Pivot (wide format) — categories as columns; built in standard SQL via conditional aggregation. Indicator — a CASE returning 1/0 (or value/NULL) marking category membership. FILTER (WHERE …) — PostgreSQL sugar for conditional aggregates. NULLIF — divide-by-zero guard returning NULL when arguments match.
Recommended Resources
• Docs: PostgreSQL manual — 'Conditional Expressions' (CASE, COALESCE, NULLIF) and 'Aggregate Expressions' for FILTER. • Read: Mode's SQL tutorial chapter on CASE for stakeholder-styled pivot practice. • Practice: take any GROUP BY two-column result you've written and pivot it wide with conditional aggregation; then add a rate column with a NULLIF guard. • Next in DSM: your logic toolkit is complete — String & Date Functions arms the other half: cleaning text keys and truncating timestamps, the raw material every CASE and GROUP BY depends on.
Recap
✓ CASE returns the first matching WHEN's value and stops — branch order IS the logic, and misordered overlapping bands create dead branches. ✓ Missing ELSE yields NULL; NULL-valued conditions fall through — give NULL its own first branch when it deserves a label. ✓ The simple form (CASE x WHEN v) is equality-only and can never match NULL; the searched form does everything. ✓ CASE is an expression: use it in SELECT, ORDER BY (business sort orders), GROUP BY, and inside aggregates. ✓ Conditional aggregation — SUM/COUNT over CASE indicators — pivots categories into columns and computes multi-metric reports in one scan. ✓ Guard rates with 100.0 (decimal division) and NULLIF (zero denominators). Next up: String & Date Functions. Your ladders and pivots are only as good as the values feeding them — next you'll standardize messy text, split and extract fields, do date arithmetic, and truncate timestamps into the monthly buckets every trend report groups by.
Run your code to see the output here.