SQL CASE WHEN: Categories, Conditional Totals and Custom Sorting

Label rows with business categories

CASE evaluates conditions top to bottom and returns the first match, so order matters: put the most specific condition first. Always add an ELSE branch, otherwise unmatched rows get NULL and vanish from grouped reports.

SELECT id, amount,
  CASE
    WHEN amount >= 1000 THEN 'Large'
    WHEN amount >= 200  THEN 'Medium'
    ELSE 'Small'
  END AS order_size
FROM orders;

Conditional totals (pivot in one query)

Putting CASE inside SUM lets you produce one column per status or per year without a pivot feature. This is the SQL equivalent of SUMIFS and works on every database engine. PostgreSQL also offers the shorter FILTER (WHERE ...) clause.

SELECT customer_id,
  SUM(CASE WHEN status = 'paid'    THEN amount ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END)          AS refunds
FROM orders
GROUP BY customer_id;

Sort by business priority

Alphabetical order rarely matches how a team works. Map each value to a rank inside ORDER BY to show urgent tickets first, then high, then the rest.

SELECT * FROM tickets
ORDER BY CASE priority
  WHEN 'urgent' THEN 1
  WHEN 'high'   THEN 2
  WHEN 'normal' THEN 3
  ELSE 4 END, created_at;

Common mistakes

CASE WHEN col = NULL never matches: use IS NULL. All branches must return compatible types, so mixing text and numbers raises an error in strict engines. Finally, avoid repeating a long CASE in SELECT, GROUP BY and ORDER BY; compute it once in a CTE and reuse the alias. If you describe the categories you need in a sentence, FormulAI writes the CASE for your engine.