CASE WHEN is SQL's if-then-else. It looks simple, but it powers some of the most useful patterns in reporting: labels, buckets, flags and pivot tables.
Examples use a tickets table for a fictional broadband provider, NetNest: ticket_id, city, category, opened_at, resolved_hours, priority.
The syntax
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE default_result
ENDConditions are checked top to bottom, and the first true one wins. Without ELSE, unmatched rows get NULL.
1. Label rows
SELECT ticket_id,
CASE WHEN resolved_hours <= 24 THEN 'Within SLA' ELSE 'Breached' END AS sla_status
FROM tickets;2. Create buckets
SELECT ticket_id, resolved_hours,
CASE
WHEN resolved_hours IS NULL THEN 'Open'
WHEN resolved_hours <= 4 THEN '0-4 h'
WHEN resolved_hours <= 24 THEN '4-24 h'
WHEN resolved_hours <= 72 THEN '1-3 days'
ELSE '3+ days'
END AS resolution_band
FROM tickets;Order matters: because the first match wins, each later condition only needs the upper limit. Handle NULL first, or it falls into ELSE.
3. Count by condition
SELECT city,
COUNT(*) AS tickets,
SUM(CASE WHEN resolved_hours <= 24 THEN 1 ELSE 0 END) AS within_sla,
ROUND(100.0 * SUM(CASE WHEN resolved_hours <= 24 THEN 1 ELSE 0 END) / COUNT(*), 1) AS sla_pct
FROM tickets
GROUP BY city;This is the SQL equivalent of Excel's COUNTIFS, for every city at once.
4. Pivot rows into columns
Ticket counts by city (rows) and category (columns):
SELECT city,
SUM(CASE WHEN category = 'No internet' THEN 1 ELSE 0 END) AS no_internet,
SUM(CASE WHEN category = 'Slow speed' THEN 1 ELSE 0 END) AS slow_speed,
SUM(CASE WHEN category = 'Billing' THEN 1 ELSE 0 END) AS billing
FROM tickets
GROUP BY city;5. Custom sort order
SELECT * FROM tickets
ORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END;Alphabetical order would put High, Low, Medium; this puts them in business order.
6. Clean inconsistent values
SELECT CASE
WHEN LOWER(TRIM(city)) IN ('bengaluru', 'bangalore') THEN 'Bengaluru'
WHEN LOWER(TRIM(city)) IN ('gurgaon', 'gurugram') THEN 'Gurugram'
ELSE TRIM(city)
END AS city_clean
FROM tickets;Common mistakes
- Overlapping conditions in the wrong order:
WHEN hours <= 72beforeWHEN hours <= 24would swallow everything. - Forgetting ELSE and getting unexpected NULLs.
- Mixing types in results (a number in one branch, text in another).
- Using COUNT with ELSE 0:
COUNT(CASE WHEN ... THEN 1 ELSE 0 END)counts every row, because 0 is not NULL. Use SUM, or COUNT without ELSE.
Practise
Our CASE WHEN set covers labels, buckets and pivots, checked instantly. Pair it with GROUP BY to build full report queries.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: