By the end of this lesson you can write simple and searched CASE expressions, order their WHEN branches correctly, and use CASE inside SELECT, GROUP BY and aggregates.
Why it matters: Almost every cleaning or reporting query relabels something: price bands, status groups, on-time versus late. CASE is how SQL says 'if this, then that', and a wrongly ordered CASE silently puts rows in the wrong bucket.
Dataset: Wadi Market online store. Examples run in the editor on the right.
Raw data is stored the way the system that produced it needed it: status codes, numbers, flags. Reports need it the way people think about it: open versus completed, budget versus premium, late versus on time. The tool that translates one into the other is the CASE expression.
CASE is an expression, not a statement. That means it produces one value per row, exactly like unit_price * 1.15 or upper(city), and you can put it anywhere a value is allowed: in the SELECT list, in WHERE, in GROUP BY, in ORDER BY, and inside aggregate functions.
PostgreSQL has two forms of CASE.
-- Simple CASE: compare ONE expression with a list of values
CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
ELSE default_result
END
-- Searched CASE: each WHEN is its own condition (any TRUE/FALSE test)
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE default_result
ENDSimple CASE: relabelling codes
Use the simple form when you are translating exact values, like a lookup: channel is 'web', 'app' or 'marketplace', and the report wants friendly names. PostgreSQL evaluates channel = 'web', then channel = 'app', and so on, and returns the result of the first match.
SELECT channel,
CASE channel
WHEN 'web' THEN 'Website'
WHEN 'app' THEN 'Mobile app'
WHEN 'marketplace' THEN 'Marketplace partner'
END AS channel_label,
count(*) AS orders
FROM orders
GROUP BY channel
ORDER BY orders DESC;Three rows, one per channel, each with a readable label next to the raw code. The website is the biggest channel. Because channel_label depends only on channel, grouping by channel is enough: PostgreSQL knows the label is the same within each group.
Searched CASE: ranges and combined conditions
The simple form can only test equality. As soon as you need ranges (unit_price < 100), lists (status IN (...)), NULL tests (delivered_at IS NULL) or conditions on several columns, use the searched form, where every WHEN holds a full condition.
Here is the rule that causes most CASE bugs: PostgreSQL checks the WHEN branches from top to bottom and stops at the first one that is TRUE. Later branches are never looked at for that row. So branches must go from the most specific condition to the most general one.
SELECT product_name,
unit_price,
CASE
WHEN unit_price < 100 THEN 'Under 100'
WHEN unit_price < 500 THEN '100-499'
ELSE '500 and above'
END AS price_band
FROM products
ORDER BY unit_price;Every product gets exactly one band. A product priced 80 is TRUE for both unit_price < 100 and unit_price < 500, but the first TRUE branch wins, so it is labelled 'Under 100'. The second branch therefore only ever sees products of 100 or more, which is why it does not need to say unit_price >= 100.
CASE inside aggregates
Because CASE produces a value per row, you can feed it to SUM or COUNT. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the rows where the condition is TRUE; SUM(CASE WHEN condition THEN amount ELSE 0 END) adds up an amount for those rows only. This is how one query produces several counts side by side, one column per category.
NULL needs care here too. A comparison involving NULL is unknown, not FALSE, so it never matches a WHEN. A parcel that has not been delivered yet (delivered_at is NULL) is neither on time nor late: if you want to count it, give it its own branch, and put that branch first.
SELECT carrier,
SUM(CASE WHEN delivered_at IS NULL THEN 1 ELSE 0 END) AS not_delivered,
SUM(CASE WHEN delivered_at::date <= promised_date THEN 1 ELSE 0 END) AS on_time,
SUM(CASE WHEN delivered_at::date > promised_date THEN 1 ELSE 0 END) AS late
FROM shipments
GROUP BY carrier
ORDER BY carrier;One row per carrier with three counts. For an undelivered parcel, both date comparisons are NULL (unknown), so those CASEs fall through to ELSE 0; only the IS NULL test catches it. The three columns add up to each carrier's total parcels. (delivered_at::date turns the delivery timestamp into a date so it compares fairly with promised_date; you will meet :: properly in the casting lesson.)
Checking your buckets
After writing a CASE, verify it the way an analyst would: group by the CASE result and count. Every row should land in exactly one bucket, the counts should add up to the table's row count, and no unexpected NULL bucket should appear. If a bucket you expected is missing, a broader branch above it is probably catching its rows first.