How do you write a CASE expression, and where can it be used?
Quick Answer
`CASE` is SQL's inline conditional expression — it evaluates a sequence of `WHEN condition THEN result` branches (optionally a final `ELSE`) and returns a single value. It can appear anywhere a value expression is allowed: `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, and inside aggregate functions for conditional aggregation.
Detailed Answer
Basic syntax
SELECT
order_id,
CASE
WHEN total > 1000 THEN 'large'
WHEN total > 100 THEN 'medium'
ELSE 'small'
END AS order_size
FROM orders;
Conditions are checked top to bottom; the first matching WHEN wins. ELSE is optional — if omitted and no branch matches, the result is NULL.
There's also a simpler "simple CASE" form for equality checks against one expression:
SELECT
CASE status
WHEN 'A' THEN 'Active'
WHEN 'I' THEN 'Inactive'
ELSE 'Unknown'
END AS status_label
FROM users;
Conditional aggregation — the most powerful use case
Combining CASE with an aggregate lets you pivot conditional counts/sums into columns without a separate query per condition:
SELECT
department,
COUNT(*) AS total_employees,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count,
SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
AVG(CASE WHEN hire_date >= '2023-01-01' THEN salary END) AS avg_new_hire_salary
FROM employees
GROUP BY department;
Note the last example: AVG(CASE WHEN ... THEN salary END) (no ELSE) — rows that don't match the condition contribute NULL, and aggregates like AVG/SUM/COUNT(column) ignore NULLs, so this correctly computes the average only over matching rows without needing a separate WHERE-filtered query.
In ORDER BY — custom sort order
SELECT * FROM tickets
ORDER BY
CASE priority
WHEN 'critical' THEN 1
WHEN 'high' THEN 2
WHEN 'medium' THEN 3
ELSE 4
END;
This is the standard trick for sorting by a business-meaningful order that doesn't match alphabetical or numeric order of the raw column.
In WHERE / GROUP BY
CASE can also drive filtering or bucketing logic:
SELECT
CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END AS age_bucket,
COUNT(*)
FROM people
GROUP BY 1; -- most engines allow grouping by output column position