SQL CASE WHEN, DATE_TRUNC and conditional aggregation explained

The idea

Raw columns rarely match the way people talk. Finance wants price bands, not prices. Sales wants months, not timestamps. Marketing wants 'CA', not ' ca'. The tools for this chapter, CASE, the date functions and the text functions, all do one thing: they compute a new value from each row.

That new value is an ordinary column from then on. You can group by it, count it, sum it and sort by it. The trick of the whole chapter is to compute the value first and then use the tools you already know.

Figure 1

CASE tests the branches top to bottom and stops at the first true one

products
product_id	name	unit_price
104	Upland Oak Bookcase	429.00
106	Northfield Rattan Sectional	229.00
107	Birch Teak Side Table	169.00
152	Monroe Stoneware Utensil Set	13.99

CASE result per row
product_id	price_band
104	200_plus
106	200_plus
107	50_to_199
152	under_50

GROUP BY price_band
price_band	products
200_plus	2
50_to_199	1
under_50	1

Step 1 of 5 Four products with a price. Finance wants bands, not prices: under 50, 50 to 199, 200 and up. CASE WHEN unit_price < 50 THEN 'under_50' WHEN unit_price < 200 THEN '50_to_199' ELSE '200_plus' END.

Four products with a price. Finance wants bands, not prices: under 50, 50 to 199, 200 and up. CASE WHEN unit_price < 50 THEN 'under_50' WHEN unit_price < 200 THEN '50_to_199' ELSE '200_plus' END.
The Utensil Set costs 13.99. The first branch, unit_price < 50, is true. The database stops there and the other branches are never looked at.
The Side Table costs 169.00. The first branch is false, the second (< 200) is true, so it gets 50_to_199. This works only because the cheap rows were already caught above.
The Bookcase and the Sectional pass no WHEN, so ELSE gives them 200_plus. Without an ELSE they would get NULL.
Now price_band is an ordinary column. GROUP BY price_band folds the four rows into three counts.

Why it exists

The data is stored for accuracy, not for reporting. An order's timestamp is exact to the second, a product has a price to the cent, and a state was typed by whoever filled in the form. None of those is the category a person wants in a chart. Before functions like these, you would export to a spreadsheet and add helper columns by hand, and repeat the work every month. In SQL the rule lives in the query, so everyone gets the same bands, the same months and the same cleaned text, every time.

Each tool also keeps the raw column intact. You can show the price and the band side by side, the timestamp and the month, the typed state and the clean one, which is the best way to check your rule against the data before trusting it.

How it works under the hood

All of these are expressions, and expressions are evaluated one row at a time, during SELECT or inside a clause that needs a value. They never look at the neighbouring rows.

CASE: first true branch wins

CASE tests its WHEN conditions in the order you wrote them, returns the value of the first one that is true, and stops. If none is true it returns the ELSE value, or NULL when there is no ELSE. Two consequences: put the narrowest condition first, and decide what should happen to rows you did not think of, instead of leaving them to fall into ELSE by accident. A NULL price fails every < comparison, so it lands in ELSE unless you test IS NULL first.

Dates: round down, then group

A timestamp is almost never equal to another one, so grouping by it is useless. DATE_TRUNC('month', order_date) rounds each value down to the first day of its month, which makes equal values appear.

Figure 2

DATE_TRUNC rounds every timestamp down to the start of its month

orders
order_id	order_date
1190	2023-01-31 12:58:16
1191	2023-01-31 15:10:33
1192	2023-01-31 15:18:57
1193	2023-02-01 13:15:26
1194	2023-02-01 16:36:32

DATE_TRUNC('month', order_date)
order_id	order_month
1190	2023-01-01
1191	2023-01-01
1192	2023-01-01
1193	2023-02-01
1194	2023-02-01

GROUP BY order_month
order_month	orders
2023-01-01	3
2023-02-01	2

Step 1 of 4 Five orders with full timestamps. No two are equal, so grouping by order_date would give five groups of one.

Five orders with full timestamps. No two are equal, so grouping by order_date would give five groups of one.
DATE_TRUNC('month', order_date) replaces each timestamp by the first moment of its month. The three January orders become 2023-01-01, whatever the day or time.
Orders 1193 and 1194 are from February 1, so they become 2023-02-01. One day later, a different group.
Equal values now exist, so GROUP BY can fold them: 3 orders in January 2023 and 2 in February. The year is part of the value, so January 2023 never mixes with January 2024.

Truncating keeps the year, so monthly revenue for three years gives 36 rows, in order. That is different from extracting the month number, which gives 1 to 12 and merges every January into one. Use truncation for a time series and extraction when you really want "all Januaries" or "which weekday". The function names vary by database: DATE_TRUNC in Postgres, DuckDB and BigQuery (with different argument order in BigQuery), DATE_FORMAT in MySQL. The idea is identical.

The time between two dates

Subtracting dates gives a duration, and a duration is a number of days: the return date minus the order date is how long an item took to come back. Both sides must be dates of the same kind. A timestamp minus a date can leave you with hours and minutes, or with a type error, depending on the engine. Cast both to DATE first and the answer is a whole number of days that you can average or bucket with a CASE. Spelling differs again between databases, so check the function once and then reuse it.

Conditional aggregation

A CASE inside an aggregate lets one GROUP BY answer several questions at once. Write the condition as 1 or 0, sum it, and you have a count of matching rows. Divide by COUNT(*) for a rate.

Figure 3

SUM(CASE ...) turns a yes/no into 1 or 0, so one GROUP BY counts both

orders
order_id	channel	status
1003	marketplace	completed
1004	marketplace	completed
1005	web	completed
1006	marketplace	cancelled
1007	marketplace	completed
1008	web	completed

CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END
order_id	channel	was_cancelled
1003	marketplace	0
1004	marketplace	0
1005	web	0
1006	marketplace	1
1007	marketplace	0
1008	web	0

GROUP BY channel
channel	orders	cancelled	cancel_rate
marketplace	4	1	25.0
web	2	0	0.0

Step 1 of 4 Which share of orders gets cancelled, per channel? You need two numbers per group: all orders, and cancelled orders.

Which share of orders gets cancelled, per channel? You need two numbers per group: all orders, and cancelled orders.
The CASE runs on each row and writes 1 for a cancelled order and 0 for the rest. Only order 1006 gets a 1.
GROUP BY channel folds the piles. COUNT(*) counts all the rows in each pile and SUM(was_cancelled) adds the 1s: marketplace has 4 orders and 1 cancelled.
Web has 2 orders and no 1s. Divide the two numbers inside the same query and you get a rate per channel: 25.0 and 0.0 percent.

This beats running one query per condition, because every number comes from the same pile of rows. Cancellation rate, return rate and share of coupon orders can sit next to each other in one result.

Text: clean before you group

TRIM, UPPER, LOWER and COALESCE return a cleaned copy of the value for this query. The table is never changed. If ' ca' and 'CA' should be the same state, clean the column in the SELECT and group by the cleaned version too, since GROUP BY sees only what you tell it to see. COALESCE(value, 'unknown') gives a NULL a label so it appears as its own row rather than as a blank.

Common misconceptions

"CASE picks the best matching branch"

It picks the first true one, top to bottom. Reverse the order of the two price branches and every product under $200 is labelled mid, including the cheap ones. No error warns you. When a band looks too large, read the branches in order as the database does.

"Grouping by the month number gives me monthly totals"

It gives you month buckets across years: January 2023, 2024 and 2025 in one row, which looks like a spike. If a month looks double what you expect, check whether the year was dropped before anything else. Truncate to the month, so the year stays in the value.

"TRIM and UPPER fix the data"

They fix the output of one query. The stray spaces are still in the table tomorrow, and a different query that forgets to TRIM will count ' CA' separately. Cleaning belongs in the query for analysis, and in the source system if you can reach it. Say which one you did when you report numbers.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

A product costs 40. Your CASE reads WHEN unit_price < 200 THEN 'mid' WHEN unit_price < 50 THEN 'cheap' ELSE 'premium' END. Which label does it get? mid, because the first true branch wins cheap, because 40 is below 50 and that is the closer fit premium, because the branches contradict each other NULL, because two branches are true