SQL CASE WHEN: If-Then Logic in a Query
CASE WHEN adds if-then logic to a query. Put every order into a small, medium or large band, learn why the order of the WHEN lines matters, then count the orders in each band with GROUP BY.
Step by step
If-then in SQL
- Sort into bands. Finance wants every order put into a size band: Small, Medium or Large. The orders table has no size column, so CASE WHEN works one out for each row, like an if-then rule.
Two bands
- CASE WHEN. Select order_id and order_total. Then add CASE WHEN order_total >= 200 THEN 'Large' and, on the next line, ELSE 'Small'. END closes the CASE and AS size names the new column. Run it. All 141 orders come back, each with a size next to it.
SELECT order_id, order_total, CASE WHEN order_total >= 200 THEN 'Large' ELSE 'Small' END AS size FROM orders; - A new column. Orders of 200 or more say Large and the rest say Small. The size column isn't stored in the table. CASE works it out each time the query runs.
Three bands
- Add a band. To add a middle band, replace the ELSE 'Small' line with WHEN order_total >= 100 THEN 'Medium' and then ELSE 'Small' on the line below. It goes after the Large line, because CASE stops at the first condition that is true. Run it again. You still get 141 rows, now split into three bands.
WHEN order_total >= 100 THEN 'Medium' ELSE 'Small' - First match wins. An order total of 111 is Medium and 452 is Large. If the Medium line came first, 452 would also pass the >= 100 test and be labelled Medium.
Count per band
- Count per band. Now count the orders in each band. Replace the first line with SELECT COUNT(*) AS orders and keep the comma at the end, because the CASE column still follows it. Then replace FROM orders; with FROM orders (no semicolon) and put GROUP BY size; on a new line. size is the name AS gave the CASE column. Run it. 3 rows come back: 17 Large orders, 40 Medium and 84 Small.
SELECT COUNT(*) AS orders,FROM orders GROUP BY size; - Mostly small. Most orders are Small. That's a real finding, from a column that doesn't exist in the table.
Recap
- The CASE WHEN rule. CASE WHEN is an if-then rule for each row. The first true condition wins, ELSE catches the rest and END closes it. GROUP BY the result to count each band.
Common mistakes
- Putting the WHEN lines in the wrong order. CASE stops at the first true condition. If WHEN order_total >= 100 comes before >= 200, an order of 452 is labelled Medium, so put the strictest condition first.
- Leaving out ELSE. Rows that match no WHEN get NULL, and GROUP BY shows them as a separate NULL band. Add ELSE so every row gets a label.
- Forgetting about NULL totals. A NULL order_total fails every WHEN, so with ELSE 'Small' it is quietly counted as Small. If blanks are possible, add WHEN order_total IS NULL THEN 'Unknown' as the first line.
- Grouping by the alias where it isn't allowed. GROUP BY size works in SQLite, MySQL and PostgreSQL, but SQL Server doesn't accept a column alias in GROUP BY. There, repeat the whole CASE expression in GROUP BY.
When a business analyst uses this
In a RAID log table, CASE WHEN risk_score >= 15 THEN 'High' WHEN risk_score >= 8 THEN 'Medium' ELSE 'Low' END gives every risk a rating. GROUP BY that rating then gives the risk counts for the steering committee pack.
Questions people ask
How do you use CASE WHEN with multiple conditions?
Join conditions in one WHEN with AND or OR, such as WHEN order_total >= 100 AND order_total < 200 THEN 'Medium'. Or add more WHEN lines, and CASE uses the first one that is true.
What does CASE WHEN return if there is no ELSE?
It returns NULL for any row that matches none of the WHEN conditions. Add ELSE to give those rows a value.
Can you use CASE WHEN in a WHERE clause?
Yes. CASE is an expression, so it can go anywhere a value can, including WHERE and ORDER BY. In WHERE, plain AND and OR conditions are often easier to read.
