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.

Labeling each item by price

The CASE expression checks the price on every row and produces a computed third column, and AS tier gives that column a name using the alias syntax from lesson 4-3.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT DISTINCT item, price,
  CASE WHEN price >= 8 THEN 'pricey' ELSE 'cheap' END AS tier
FROM orders
ORDER BY item;

Output

bagel|4|cheap
coffee|5|cheap
sandwich|8|pricey

The DISTINCT collapses the six order rows down to the three unique item and price combinations, which keeps the output short enough to read. CASE itself runs per row and knows nothing about grouping.

Note the boundary. price >= 8 puts the 8-cost sandwich in the pricey tier, and writing price > 8 instead would have labeled everything cheap. Off-by-one boundaries in tier definitions are worth checking against real data every time.

Counting only some rows with CASE inside SUM

This is the trick that makes CASE indispensable. COUNT(*) with a WHERE can count coffees, but then the query counts only coffees, and a dashboard usually wants each customer's coffee count alongside their total orders.

Putting the condition inside the aggregate solves it. 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 is called conditional aggregation, and it is how one query produces a dashboard line such as "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 have to be counted in total.

The same shape scales to several columns at once. Three SUM(CASE ...) expressions in one SELECT give three independent counts over the same buckets, which is how a report shows orders this week, last week, and all time side by side.

Coffee count and total count in one pass

One output row per customer, carrying their coffee orders and their total orders side by side from a single scan of the table.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

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

Output

Ana|1|3
Ben|1|2
Cara|1|1

Ana ordered one coffee out of three orders, Ben one out of two, and Cara one out of one. Every one of Ana's three rows contributed to total, while only the coffee row contributed a 1 to coffees.

A WHERE item = 'coffee' could not produce this result. It would have deleted the bagel and sandwich rows for the entire query, and total would then have reported 1 for all three customers, which is a different and much less interesting report.

A CASE with no match and no ELSE

When neither WHEN test passes and no ELSE is present, CASE produces NULL, the same no-value marker from lesson 2-3.

Nothing warns about it. The query succeeds and a column that was supposed to hold labels quietly holds gaps instead, which then propagate: those rows will not match = 'active' or != 'active', and a later COUNT of the column will come out short.

CASE
  WHEN status = 1 THEN 'active'
  WHEN status = 2 THEN 'dormant'
  ELSE 'unknown'
END

Most style guides require an explicit ELSE for exactly this reason. A visible unknown label is far easier to debug than a NULL that came from a branch nobody thought about.

Labeling each customer by total spend

Because a CASE in the SELECT list is evaluated after the buckets are built, its test can use SUM(price) directly.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT customer, SUM(price) AS total,
  CASE WHEN SUM(price) > 10 THEN 'big spender' ELSE 'regular' END AS label
FROM orders
GROUP BY customer
ORDER BY customer;

Output

Ana|17|big spender
Ben|13|big spender
Cara|5|regular

The third column is a CASE whose test is SUM(price) > 10 and whose two results are the two labels. Ana at 17 and Ben at 13 clear the threshold, Cara at 5 does not.

Compare this with the previous lesson. HAVING SUM(price) > 10 would have removed Cara from the result entirely, while CASE keeps her row and merely labels it differently. Filtering and labeling are separate jobs, and reports usually want the second one.