SQL CASE WHEN: Multiple Conditions, ELSE, GROUP BY
· 7 min read
SQL CASE WHEN (often called the SQL CASE statement) is SQL's if/then/else. It checks conditions from top to bottom, returns the value of the first one that is true, and falls back to ELSE if none are. You can use it anywhere a value is allowed: in SELECT, WHERE, GROUP BY, ORDER BY or inside SUM.
SELECT line_id, product, quantity * unit_price AS line_value,
CASE
WHEN quantity * unit_price >= 500 THEN 'High'
WHEN quantity * unit_price >= 100 THEN 'Mid'
ELSE 'Low'
END AS value_band
FROM order_lines
ORDER BY line_id;Every CASE ends with END, and the alias after END names the new column. Below: the output of that query, multiple conditions, the ELSE rule, CASE WHEN in GROUP BY, and the ordering mistake that silently empties a bucket.
A worked example
| line_id | product | category | quantity | unit_price |
|---|---|---|---|---|
| 1 | Copy paper | Office | 40 | 5.00 |
| 2 | Stapler | Office | 3 | 12.00 |
| 3 | Coffee beans | Kitchen | 20 | 30.00 |
| 4 | Paper cups | Kitchen | 100 | 0.50 |
| 5 | Floor cleaner | Cleaning | 12 | 15.00 |
| 6 | Mop | Cleaning | 2 | 25.00 |
| 7 | Toner | Office | 6 | 90.00 |
The query above returns:
| line_id | product | line_value | value_band |
|---|---|---|---|
| 1 | Copy paper | 200.00 | Mid |
| 2 | Stapler | 36.00 | Low |
| 3 | Coffee beans | 600.00 | High |
| 4 | Paper cups | 50.00 | Low |
| 5 | Floor cleaner | 180.00 | Mid |
| 6 | Mop | 50.00 | Low |
| 7 | Toner | 540.00 | High |
Look at Coffee beans: 600 is both >= 500 and >= 100. It gets 'High' because that branch is checked first, and CASE stops at the first match. That one rule explains most CASE bugs.
CASE WHEN with multiple conditions
Combine conditions inside one WHEN with AND and OR, exactly as in a WHERE clause:
SELECT product, category, quantity,
CASE
WHEN category = 'Office' AND quantity >= 20 THEN 'Bulk office'
WHEN category = 'Kitchen' OR category = 'Cleaning' THEN 'Facilities'
ELSE 'Standard'
END AS team
FROM order_lines
ORDER BY line_id;| product | category | quantity | team |
|---|---|---|---|
| Copy paper | Office | 40 | Bulk office |
| Stapler | Office | 3 | Standard |
| Coffee beans | Kitchen | 20 | Facilities |
| Paper cups | Kitchen | 100 | Facilities |
| Floor cleaner | Cleaning | 12 | Facilities |
| Mop | Cleaning | 2 | Facilities |
| Toner | Office | 6 | Standard |
When you mix AND and OR in one condition, add brackets. a AND b OR c means (a AND b) OR c, which is often not what you meant. category IN ('Kitchen', 'Cleaning') is a tidier way to write the OR.
The short form: CASE column WHEN value
If every branch compares the same column to a fixed value, you can write the column once:
SELECT product,
CASE category
WHEN 'Office' THEN 'OF'
WHEN 'Kitchen' THEN 'KT'
ELSE 'CL'
END AS code
FROM order_lines
ORDER BY line_id;| product | code |
|---|---|
| Copy paper | OF |
| Stapler | OF |
| Coffee beans | KT |
| Paper cups | KT |
| Floor cleaner | CL |
| Mop | CL |
| Toner | OF |
This form only does equality. For ranges like >= 500, use the full CASE WHEN form.
CASE WHEN ELSE: what happens without it
ELSE is optional. Leave it out and any row that matches no branch gets NULL:
SELECT product, quantity,
CASE WHEN quantity >= 20 THEN 'Big order' END AS flag
FROM order_lines
ORDER BY line_id;| product | quantity | flag |
|---|---|---|
| Copy paper | 40 | Big order |
| Stapler | 3 | NULL |
| Coffee beans | 20 | Big order |
| Paper cups | 100 | Big order |
| Floor cleaner | 12 | NULL |
| Mop | 2 | NULL |
| Toner | 6 | NULL |
Sometimes that is fine. But if you later group by this column, all the NULLs land in one unnamed group, and WHERE flag <> 'Big order' will not find them, because comparisons with NULL are never true. Write an explicit ELSE unless you want NULL.
CASE WHEN in GROUP BY
To count or sum by your new buckets, group by the CASE expression. Most databases (Postgres, MySQL, DuckDB, SQLite) accept GROUP BY 1, meaning "the first column in SELECT":
SELECT CASE
WHEN quantity * unit_price >= 500 THEN 'High'
WHEN quantity * unit_price >= 100 THEN 'Mid'
ELSE 'Low'
END AS value_band,
COUNT(*) AS lines,
SUM(quantity * unit_price) AS total_value
FROM order_lines
GROUP BY 1
ORDER BY total_value DESC;| value_band | lines | total_value |
|---|---|---|
| High | 2 | 1140.00 |
| Mid | 2 | 380.00 |
| Low | 3 | 136.00 |
SQL Server does not allow GROUP BY 1 or grouping by the alias, so there you repeat the whole CASE expression in GROUP BY. That version runs everywhere:
SELECT CASE
WHEN quantity * unit_price >= 500 THEN 'High'
WHEN quantity * unit_price >= 100 THEN 'Mid'
ELSE 'Low'
END AS value_band,
COUNT(*) AS lines
FROM order_lines
GROUP BY CASE
WHEN quantity * unit_price >= 500 THEN 'High'
WHEN quantity * unit_price >= 100 THEN 'Mid'
ELSE 'Low'
END
ORDER BY lines DESC, value_band;| value_band | lines |
|---|---|
| Low | 3 |
| High | 2 |
| Mid | 2 |
For more on grouping, see GROUP BY multiple columns.
CASE inside SUM: conditional counts
Put CASE inside an aggregate to count or sum only some rows, side by side in one query:
SELECT category,
SUM(CASE WHEN quantity >= 10 THEN 1 ELSE 0 END) AS lines_10_plus,
SUM(CASE WHEN quantity < 10 THEN 1 ELSE 0 END) AS lines_under_10
FROM order_lines
GROUP BY category
ORDER BY category;| category | lines_10_plus | lines_under_10 |
|---|---|---|
| Cleaning | 1 | 1 |
| Kitchen | 2 | 0 |
| Office | 1 | 2 |
This is the SQL way to build a small pivot table. Postgres and DuckDB also support COUNT(*) FILTER (WHERE quantity >= 10), which does the same thing.
Common mistakes
Branches in the wrong order (the AI mistake to watch for)
AI assistants often write the branches in the order the buckets were mentioned, not the order that works. Ask for "Mid for 100 and up, High for 500 and up" and you may get this:
SELECT CASE
WHEN quantity * unit_price >= 100 THEN 'Mid'
WHEN quantity * unit_price >= 500 THEN 'High'
ELSE 'Low'
END AS value_band,
COUNT(*) AS lines
FROM order_lines
GROUP BY 1
ORDER BY lines DESC;| value_band | lines |
|---|---|
| Mid | 4 |
| Low | 3 |
There is no 'High' row at all. Every line worth 500 or more is also worth 100 or more, so it hits the 'Mid' branch first and the 'High' branch can never fire. No error, no warning.
How to spot it: count the buckets in the output. If a bucket you asked for is missing or has zero rows, check the branch order. With "greater than" thresholds, the biggest threshold goes first. With "less than" thresholds, the smallest goes first. Or make each branch self-contained, such as WHEN line_value >= 100 AND line_value < 500.
Mixing data types between branches
THEN 'High' in one branch and THEN 0 in another will error in Postgres and DuckDB, and quietly convert values in some other databases. Keep every branch the same type.
Testing for NULL with =
WHEN discount = NULL is never true. Use WHEN discount IS NULL.
FAQ
Can you have multiple conditions in a CASE WHEN statement?
Yes, in two ways: several WHEN branches, each with its own condition, and AND/OR inside one branch. Both are shown above.
Can I use CASE WHEN in a WHERE clause?
Yes, it returns a value like any expression, so WHERE CASE ... END = 'High' works. Usually a plain condition is clearer, though.
What does CASE return if no condition matches and there is no ELSE?
It returns NULL for that row. Add an ELSE branch when you want a default value instead.
Is CASE WHEN the same in MySQL, Postgres and SQL Server?
Yes. CASE WHEN ... THEN ... ELSE ... END is standard SQL and works the same in all of them. MySQL also has an IF() function and SQL Server has IIF(), but CASE is portable.
CASE also turns up in SQL interview questions for data analysts, often combined with SUM.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “The AI's order sizes have no Bulk” →