Filtering Updated November 2026

CASE WHEN

CASE WHEN condition THEN value WHEN ... ELSE other END evaluates conditions in order and returns the value of the first one that is true, or the ELSE value (NULL if there is no ELSE). It is SQL's if-then-else and works anywhere an expression can go: SELECT lists, WHERE, ORDER BY and inside aggregates.

Syntax

CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

Part	What it is
`WHEN condition`	Any boolean expression; tested top to bottom
`THEN result`	Value returned for the first true condition
`ELSE result`	Value when nothing matches; NULL if omitted

Examples

SELECT name, salary,
  CASE WHEN salary >= 8000 THEN 'high'
       WHEN salary >= 4000 THEN 'mid'
       ELSE 'low' END AS band
FROM employees;

Result: name | salary | band Ana | 9000 | high Ben | 4500 | mid Carl | 2500 | low

Order matters: 9000 matches the first WHEN and stops there.

SELECT
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;

Result: paid | pending 310 | 45

Conditional aggregation, the classic way to pivot statuses into columns. PostgreSQL also has COUNT(*) FILTER (WHERE status = 'paid').

SELECT name, status FROM tasks
ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'open' THEN 2 ELSE 3 END;

Result: Urgent tasks first, then open, then everything else

The simple CASE form (CASE expr WHEN value) compares one expression against values.

Common mistakes

Forgetting END raises a syntax error; every CASE must close with END (not END CASE in SQLite or PostgreSQL).
Omitting ELSE makes unmatched rows NULL, which then disappears from SUMs and comparisons; add ELSE 0 or ELSE 'other'.
Mixing types in THEN branches (THEN 'none' in one and THEN 0 in another) fails in PostgreSQL; return the same type in every branch.

Practice CASE WHEN

These drills check your answer on live data, free, no account needed.

Label with CASEReturn each order id with a label: large when the amount is 150 or more, otherwise small.
Pivot the statusesReturn one row with three columns: the number of paid orders, refunded orders and pending orders, in that order.

Questions

Can I use CASE in a WHERE clause?

Yes, but usually plain AND/OR is clearer. CASE in WHERE is useful when the comparison itself depends on a parameter, such as WHERE amount > CASE WHEN region = 'EU' THEN 100 ELSE 50 END.

How do I pivot rows into columns in SQL?

Use one aggregate per column with a CASE inside: SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS jan, and so on, with GROUP BY on the row dimension.

COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.
GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.