CASE — Conditional Logic Inside SQL
Use CASE WHEN ... THEN ... ELSE ... END to bucket, label, or transform rows inline. Works in SELECT, WHERE, ORDER BY, GROUP BY, and aggregate filters.
- CASE
- WHEN
- THEN
- ELSE
- conditional
- bucketing
Use this when you need to:
- Bucket continuous values into named tiers — "low / medium / high" GPAs, "small / medium / large" orders, age ranges
- Translate codes into human-readable labels in a report — "A" → "Pass", "F" → "Fail"
- Apply if-else logic inline — pick a value or a default depending on a condition
- Conditionally count or sum within an aggregate — "active users this month" without a separate query
- Sort by custom rules — pinned rows first, then everything else
CASE is SQL's if-else. It evaluates conditions in order and returns the first matching THEN value, or the ELSE value if nothing matches. CASE works anywhere you can put a value — SELECT lists, WHERE clauses, ORDER BY, even inside aggregates.
Basic Syntax
CASE WHEN condition_1 THEN value_1 WHEN condition_2 THEN value_2 ... ELSE default_value END
Bucketing — turn numbers into labels
SELECT
applications_id,
appl_hs_gpa,
CASE
WHEN appl_hs_gpa IS NULL THEN 'Not reported' -- test NULL first, or ELSE catches it
WHEN appl_hs_gpa >= 3.7 THEN 'High'
WHEN appl_hs_gpa >= 3.0 THEN 'Mid'
ELSE 'Low'
END AS gpa_tier
FROM ods_applications;Conditional Aggregation
-- Count first-generation vs. continuing-generation people per state SELECT residence_state, COUNT(*) FILTER (WHERE first_gen_ind = 'Y') AS first_gen, COUNT(*) FILTER (WHERE first_gen_ind = 'N') AS continuing_gen FROM ods_person GROUP BY residence_state ORDER BY residence_state; -- Or with classic CASE — works on every database: SELECT residence_state, SUM(CASE WHEN first_gen_ind = 'Y' THEN 1 ELSE 0 END) AS first_gen, SUM(CASE WHEN first_gen_ind = 'N' THEN 1 ELSE 0 END) AS continuing_gen FROM ods_person GROUP BY residence_state ORDER BY residence_state; -- Rows with first_gen_ind NULL land in neither column, so the two -- counts can add up to less than the state's headcount. That is correct.
PostgreSQL's FILTER (WHERE ...) is the modern, more readable alternative to SUM(CASE WHEN ... THEN 1 ELSE 0 END). Reach for FILTER when you can; the CASE form still works and is portable across databases.
Custom Sort Order
-- Pin "New" applications (still awaiting a decision) to the top, -- then everything else, newest first SELECT applications_id, current_status_desc, appl_date FROM ods_applications ORDER BY CASE appl_current_status WHEN 'NEW' THEN 0 ELSE 1 END, appl_date DESC;
Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.