If-Then Inside a Query: CASE, and the Art of the Bucket

CASE is SQL’s conditional expression — its if-then-else. It has quietly powered the last two chapters: the sum(CASE ...) pivot, the GROUPING() labels. It deserves a chapter of its own because it is the single most useful tool for turning raw column values into the categories an analysis actually reasons about. Prices become tiers, dates become periods, statuses become “good” and “bad.” CASE is where a query stops reporting the data as stored and starts reporting it as you think about it. Crucially, it’s an expression, not a statement — it evaluates to a value, so it can go anywhere a value can: the SELECT list, WHERE, ORDER BY, GROUP BY, inside an aggregate.

The two forms

CASE comes in two shapes. The searched form lists independent boolean conditions and returns the value for the first that’s true:

CASE
  WHEN price < 20 THEN 'budget'
  WHEN price < 40 THEN 'standard'
  ELSE 'premium'
END

Conditions are tested top to bottom, and the first match wins — which is why the ranges don’t need explicit lower bounds. A price of 15 matches < 20 and stops; a price of 30 fails < 20, matches < 40, and stops. Order matters. If you wrote < 40 first, everything under 40 would grab the standard label and budget would never fire. The ELSE catches everything left over; omit it and unmatched rows return NULL, which is a common and quiet source of surprise NULLs.

The simple form compares one expression against a list of values, like a switch:

CASE status
  WHEN 'placed'  THEN 1
  WHEN 'shipped' THEN 2
  ELSE 99
END

It’s shorter when you’re testing one expression for equality against constants. The searched form is strictly more general — it handles ranges, IS NULL, compound conditions, comparisons between columns — so when in doubt, use it. One trap in the simple form: it compares with =, and NULL = anything is never true, so a WHEN NULL THEN ... branch never fires. To handle a NULL you must use the searched form with WHEN status IS NULL.

Two more things hold across both forms. All the THEN results must unify to a single type; mixing a number and a string is an error, since a column can hold only one type. And the branches evaluate lazily, left to right, so a later branch that would error — a division by zero, say — is never reached once an earlier one matches.

Bucketing: continuous values into labeled tiers

The most common analytical use of CASE is bucketing: cutting a continuous column into a handful of named bands so you can group by them. Book prices run from about 5 to 55; here they become three tiers, and grouping by the tier tells us how the catalog is distributed:

SELECT
  CASE
    WHEN price < 20 THEN 'budget'
    WHEN price < 40 THEN 'standard'
    ELSE 'premium'
  END AS tier,
  count(*)   AS books,
  min(price) AS lo,
  max(price) AS hi
FROM books
GROUP BY tier
ORDER BY min(price);

   tier   | books |  lo   |  hi
----------+-------+-------+-------
 budget   |   174 |  5.14 | 19.95
 standard |   235 | 20.20 | 39.89
 premium  |   191 | 40.01 | 54.77
(3 rows)

Two things are worth pausing on. First, GROUP BY tier reuses the SELECT alias. Postgres lets a GROUP BY name a select-list alias, so you don’t repeat the whole CASE block. (Not every database does; SQL Server, for one, makes you write the expression out again or wrap it in a subquery.) Second, the min/max per tier confirm the cuts landed where intended: budget tops out at 19.95, standard at 39.89, and premium starts at 40.01. The boundaries are clean because the < comparisons are exclusive on the upper end.

CASE in ORDER BY: a custom sort order

Order status has a natural lifecycle: placed, shipped, delivered, and then the exceptions. But sorting it alphabetically scrambles that into “cancelled, delivered, placed…” A CASE in the ORDER BY maps each status to a rank and sorts on that instead. You get a meaningful sequence with no extra table:

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY CASE status
           WHEN 'placed'    THEN 1
           WHEN 'shipped'   THEN 2
           WHEN 'delivered' THEN 3
           WHEN 'returned'  THEN 4
           WHEN 'cancelled' THEN 5
         END;

  status   | orders
-----------+--------
 placed    |   9815
 shipped   |  10039
 delivered |  24944
 returned  |  10161
 cancelled |   5041
(5 rows)

The rows now read in lifecycle order rather than alphabetical. The same trick handles “put NULLs where I want them” or “pin one special value to the top.” It works anywhere the sort you want isn’t the sort the column’s type gives you.

Conditional aggregation, again

The idiom that keeps returning — a CASE (or FILTER) inside an aggregate — is worth naming as the workhorse it is. It computes several conditional summaries in one pass. Here it produces a per-genre return rate: for each genre, what fraction of its order lines belong to a returned order. count(*) FILTER (WHERE ...) counts the returned lines, NULLIF(count(*), 0) guards the division, and the 100.0 * keeps it out of integer division:

SELECT b.genre,
       round(100.0 * count(*) FILTER (WHERE o.status = 'returned')
             / NULLIF(count(*), 0), 1) AS return_pct
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN books b        ON b.book_id = oi.book_id
GROUP BY b.genre
ORDER BY return_pct DESC;

   genre    | return_pct
------------+------------
 Nonfiction |       17.0
 Poetry     |       17.0
 Sci-Fi     |       17.0
 History    |       16.9
 Fiction    |       16.8
 Children   |       16.6
(6 rows)

Return rates are flat across genres, near 17% — which is itself a finding, that returns don’t track genre. The mechanics are the point: one grouped scan yielded a computed rate per category, with the ratio’s numerator conditionally counted.

COALESCE and NULLIF: CASE with a shorter name

Two functions you’ll reach for constantly are just CASE in disguise, and knowing that demystifies them. NULLIF(a, b) returns NULL when a = b, which is exactly CASE WHEN a = b THEN NULL ELSE a END — its one great use is guarding division, turning a would-be divide-by-zero into a clean NULL, as above. COALESCE(a, b, c) returns the first non-NULL of its arguments — CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. It’s how you supply a default for a missing value. A left join that finds no match leaves NULLs, and COALESCE(..., 0) turns “no sales” into a real zero:

SELECT a.name,
       COALESCE(sum(oi.quantity * oi.unit_price), 0) AS scifi_revenue
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id AND b.genre = 'Sci-Fi'
LEFT JOIN order_items oi ON oi.book_id = b.book_id
GROUP BY a.author_id, a.name
ORDER BY scifi_revenue ASC
LIMIT 4;

   name    | scifi_revenue
-----------+---------------
 Author 21 |             0
 Author 27 |             0
 Author 25 |             0
 Author 8  |             0
(4 rows)

Authors with no Sci-Fi title show 0, not NULL, because COALESCE caught the empty sum. Both functions are standard SQL and work everywhere; they’re worth using over a hand-written CASE precisely because they announce intent in one word.

Final thoughts

CASE is how a query classifies. It reshapes raw values into the categories your analysis is actually about. It comes in two forms — searched for ranges and conditions, simple for equality against constants. And because it’s an expression, it drops into SELECT, ORDER BY, GROUP BY, and most valuably inside aggregates, where conditional aggregation lets one pass compute many summaries. Its exclusive-boundary bucketing gives clean tiers; NULLIF and COALESCE are the named shortcuts for the two CASE patterns you write most, guarding division and filling defaults. Remember the top-to-bottom, first-match rule and the silent NULL when ELSE is missing, and CASE becomes the most reliable lever you have for bending data into shape. Next we turn to the one data type with the most rules of all: time.

Next: Working with time