Level 1 · Products by price band

CASE is SQL’s if/else. It returns a value based on conditions:

CASE
  WHEN unit_price < 50  THEN 'under_50'
  WHEN unit_price < 200 THEN '50_to_199'
  ELSE '200_plus'
END

It can go anywhere an expression goes: in SELECT, WHERE, ORDER BY, GROUP BY, or inside an aggregate.

Branches are checked in order

The first true branch wins and the rest are skipped. That is why the second branch can simply say unit_price < 200: a $30 product already matched the first branch and never reaches it. Order your branches from the most specific or lowest boundary to the highest. If you reversed them and checked < 200 first, every cheap product would land in 50_to_199.

Be precise about boundaries. A product at exactly $50.00 must go into exactly one band. With < boundaries checked in ascending order, each value falls into exactly one bucket with no gaps.

ELSE and NULL

If no branch matches and there is no ELSE, CASE returns NULL. Here every product has a price, so ELSE '200_plus' is safe. When a column can be NULL, handle it explicitly first, or it falls into the ELSE bucket and inflates it:

CASE
  WHEN unit_cost IS NULL THEN 'unknown'
  WHEN unit_cost < 20    THEN 'low'
  ELSE 'high'
END

Grouping by a CASE

You need the same expression in GROUP BY. Repeating the whole CASE is legal but noisy, so analysts usually write GROUP BY 1 (the first select column) or the alias:

SELECT
  CASE WHEN unit_price < 50 THEN 'under_50'
       WHEN unit_price < 200 THEN '50_to_199'
       ELSE '200_plus' END AS price_band,
  COUNT(*) AS products
FROM products
GROUP BY price_band;

Grouping by an alias works in DuckDB, Postgres, MySQL and BigQuery. SQL Server requires the full expression.

Sorting buckets in a sensible order

Text labels sort alphabetically: 200_plus comes before 50_to_199. To show bands from cheapest to priciest, sort by a second CASE or by MIN(unit_price):

ORDER BY MIN(unit_price)

Simple CASE

There is also a short form that compares one expression to values:

CASE channel WHEN 'web' THEN 'Website' WHEN 'app' THEN 'Mobile app' ELSE 'Partner' END

It only handles equality, and CASE col WHEN NULL never matches, for the same reason = NULL never does.

Common mistakes

• Overlapping conditions in the wrong order, as above.
• Mixing types between branches, such as THEN 'low' and THEN 0. Every branch must return the same type, or the database errors (or casts in surprising ways).