SQL CASE WHEN: IF logic in a query
CASE WHEN turns rules into a new column. SQL checks each WHEN from top to bottom and uses the first one that's true; ELSE catches the rest. Leave ELSE out and unmatched rows get an empty value.
Business uses: bucket deals by size, flag late tickets, group countries into regions, label plans as free or paid.
Syntax
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE fallback
END| id | priority | speed |
|---|---|---|
| 1 | normal | within a day |
| 2 | low | took longer |
| 3 | low | took longer |
| 4 | low | took longer |
| 5 | low | took longer |
| 6 | normal | took longer |
| 7 | normal | within a day |
| 8 | normal | within a day |
| 9 | low | within a day |
| 10 | normal | within a day |
Showing 10 of 12 rows. Press Run to see them all.
CASE inside a total
Put CASE inside SUM or COUNT to get several conditional totals in one row, the SQL version of COUNTIFS side by side:
SELECT
SUM(CASE WHEN priority = 'urgent' THEN 1 ELSE 0 END) AS urgent,
SUM(CASE WHEN priority = 'low' THEN 1 ELSE 0 END) AS low
FROM tickets;CASE works the same in every major database. Some also have a shorter IF() or IIF() for a single condition.
Your turn
Label each order 'big' when quantity is 4 or more, otherwise 'small', as size, and count orders per size as orders.
CASE WHEN quantity >= 4 THEN ... ELSE ... END AS size, then GROUP BY size.
This page on one card
