CASE: Decisions Inside a Query

Labeling rows without leaving SQL

Reports constantly need labels and buckets: mark each order cheap or pricey, each user active or dormant, each payment ok or overdue. You could fetch every row and label it in app code, but then the database can no longer group, filter, or sort by the label, and you are back to hand-rolling what SQL already does. A CASE expression computes a new value for each row, right inside the query:

CASE
  WHEN test THEN result
  WHEN other_test THEN other_result
  ELSE fallback
END

The database checks the WHEN tests top to bottom and uses the first one that passes. CASE is an expression, meaning it produces a value, so it goes anywhere a value can go: in the SELECT list, inside ORDER BY, even inside an aggregate function (more on that below). If no test passes and there is no ELSE, the result is NULL, the no-value marker from lesson 2-3.

Counting only some rows: CASE inside SUM

Here is the trick that makes CASE indispensable. COUNT(*) with a WHERE can count coffees, but then the query counts only coffees. What if one query should report each customer's coffee count and their total orders? Put the condition inside the aggregate: CASE WHEN item = 'coffee' THEN 1 ELSE 0 END turns each row into a 1 or a 0, and SUM adds them up, so only the matching rows contribute.

SELECT customer,
  SUM(CASE WHEN item = 'coffee' THEN 1 ELSE 0 END) AS coffees,
  COUNT(*) AS total
FROM orders
GROUP BY customer;

This pattern, conditional aggregation, is how one query produces a dashboard row like "Ana: 1 coffee out of 3 orders". WHERE could never do it, because WHERE drops rows for the whole query, and here the non-coffee rows still need to be counted in total.