SQL CASE WHEN: Conditional Logic in Queries
CASE WHEN Statement is a SQL query that case evaluates conditions top to bottom. Formula Genius generates and validates this formula automatically from a plain-English prompt.
CASE WHEN is SQL's IF-THEN-ELSE. Categorize data, create conditional aggregations, and build pivot-like outputs.
The Formula
"Categorize orders as Small, Medium, or Large based on amount"
SELECT order_id, amount,
CASE
WHEN amount < 100 THEN 'Small'
WHEN amount < 1000 THEN 'Medium'
ELSE 'Large'
END AS order_size
FROM orders;CASE evaluates conditions top to bottom. First match wins. An order of $50 hits 'Small', $500 hits 'Medium', $5000 falls through to 'Large'. The result appears as a new column called order_size.
Step-by-Step Breakdown
- CASE opens the conditional block
- WHEN amount < 100 THEN 'Small' — first condition checked
- WHEN amount < 1000 THEN 'Medium' — only checked if first is false
- ELSE 'Large' — catches everything remaining
- END AS order_size — closes CASE and names the output column
Edge Cases & Warnings
- Without ELSE, non-matching rows return NULL
- Conditions are evaluated in order — put the most restrictive first
- CASE works in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING
- NULL comparisons need IS NULL, not = NULL
Examples
"amount = 50"
Small"amount = 500"
Medium"amount = 5000"
LargeFrequently Asked Questions
Can I use CASE in GROUP BY?
Yes. You can group by the CASE expression or reference the alias in databases that support it. In standard SQL, repeat the CASE in GROUP BY.
How do I do conditional aggregation?
SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) counts active records. This creates pivot-table-like outputs.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.