CASE WHEN

Game: chapter VII

SQL's if-then-else. CASE computes a value row by row, and once you can compute a label, you can group by it.

The shape of a CASE expression

A CASE expression lists conditions in order and returns the value of the first one that is true:

CASE
  WHEN condition_1 THEN value_1
  WHEN condition_2 THEN value_2
  ELSE other_value
END

The database tests the conditions from top to bottom and stops at the first true one, so the order matters when conditions overlap. If none is true, the result is the ELSE value, or NULL when there is no ELSE. A CASE is an expression, not a statement: it goes wherever a value can go, in SELECT, WHERE, ORDER BY, GROUP BY, or inside an aggregate. The examples run on the bakery database from the bakery database page.

Labelling rows

The simplest use turns a number into a word. Here each of the ten products the bakery opened with (ids 1 to 10) receives a price band. Because the conditions are tested in order, the second one does not need to say "and at least 2": anything below 2 has already been caught by the first.

SELECT name, price,
       CASE
         WHEN price < 2 THEN 'small'
         WHEN price < 3.5 THEN 'medium'
         ELSE 'large'
       END AS band
FROM product
WHERE id <= 10
ORDER BY price, name;

Buckets and GROUP BY

CASE becomes most useful when you group by the label. Times of day are a classic case: the bakery wants to know how busy each part of the day is. No column says "morning" or "afternoon", but a CASE can compute it from time, and GROUP BY can then count the orders per bucket. Because the times are written HH:MM with a leading zero, comparing them as text gives the right answer.

SELECT CASE
         WHEN time < '09:00' THEN 'early'
         WHEN time < '12:00' THEN 'morning'
         WHEN time < '17:00' THEN 'afternoon'
         ELSE 'evening'
       END AS part_of_day,
       COUNT(*) AS orders
FROM orders
GROUP BY part_of_day
ORDER BY orders DESC, part_of_day;

SQLite and PostgreSQL both let you group by the alias, as here, as long as the alias is used on its own. Some databases require you to repeat the whole CASE in the GROUP BY, or to compute the label in a subquery or a CTE first and group in the outer query. The CTE version is often the clearest: one step labels, the next step counts.

Watch the boundaries. With < '12:00' an order at exactly noon belongs to the afternoon; with <= '12:00' it belongs to the morning. Decide on purpose, and check whether any rows sit exactly on a boundary. Here five orders were placed at exactly 12:00, and the query above counts them in the afternoon; with <= '12:00' they would move to the morning, and the two buckets would both read 86. Likewise the three orders at exactly 09:00 are in the morning, not the early bucket. A bucket that receives no rows does not appear in the result at all, which is easy to miss when you expect to see four lines and get three.

Conditional counts and sums

A CASE inside an aggregate counts or adds only some rows, which lets one query produce several figures side by side. SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts the rows that meet the condition. The same idea with a quantity in the THEN branch adds up only part of a column. The example covers the week of Monday 9 March.

SELECT day,
       COUNT(*) AS orders,
       SUM(CASE WHEN time < '12:00' THEN 1 ELSE 0 END) AS before_noon,
       SUM(CASE WHEN time >= '12:00' THEN 1 ELSE 0 END) AS after_noon
FROM orders
WHERE day BETWEEN '2026-03-09' AND '2026-03-15'
GROUP BY day
ORDER BY day;

Monday's order at exactly 12:00 is counted after noon, because the two conditions split at < '12:00' and >= '12:00': every order falls in exactly one column, and the two add up to orders. Friday 13 March has no row at all: the shop is closed on Fridays, and GROUP BY only makes groups for days that have orders. This is often called pivoting: values that were rows (before noon, after noon) become columns. It is a compact way to compare categories without running one query per category.

CASE in ORDER BY

Sometimes the natural order of a column is not the order you want. Alphabetical order puts "bread" before "cake" before "pastry", but a shop counter shows pastries first. A CASE in ORDER BY gives each value a rank of your choosing.

SELECT category
FROM product
GROUP BY category
ORDER BY CASE category WHEN 'pastry' THEN 1 WHEN 'bread' THEN 2 ELSE 3 END;

GROUP BY keeps one row per category, and the CASE only uses the grouped column. (With SELECT DISTINCT instead, the sort would be an error: see DISTINCT and ORDER BY.) This is the short form, CASE column WHEN value THEN ..., which compares one column against a list of values. It cannot test ranges or NULLs, for which you need the long form.

Grammar: CASE, not IIF

CASE is the one standard way to choose between values. The shorthand IIF(condition, a, b) is not standard SQL, and neither is MAX or MIN with two arguments, which compares two values on one row: both are written as a CASE. A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own.

SELECT name, IIF(price > 3, 'dear', 'cheap') AS label
FROM product;

SELECT name, CASE WHEN price > 3 THEN 'dear' ELSE 'cheap' END AS label
FROM product
WHERE category = 'cake'
ORDER BY id;

Common mistakes

Forgetting END, which produces a confusing syntax error further down the query. Writing overlapping conditions in the wrong order, so that a broad condition placed first catches rows meant for a narrower one below it. Leaving out ELSE and getting NULL labels for the rows nobody thought of. Testing for NULL in the short form, where WHEN NULL never matches. Comparing times stored without a leading zero, where '9:30' sorts after '12:00'.

Try it: Morning people, on the exercises page.

In the game: Chapter VII, The Quietest Hour, practises CASE WHEN with GROUP BY.