Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Two forms

Form	Use it when
Searched CASE	Each branch has its own condition (ranges, AND/OR).
Simple CASE	You compare one expression to several values.

Branches are checked top to bottom; the first true one wins. No match and noELSE returns NULL.

Searched CASE: bucket a value

Ranges and conditions

case
  when quantity_on_hand = 0  then 'Out of stock'
  when quantity_on_hand < 10 then 'Low'
  else 'Healthy'
end

Simple CASE: map values

One expression, several values

case status
  when 'shipped'   then 'Done'
  when 'cancelled' then 'Closed'
  else 'In progress'
end

Conditional aggregation

Put CASE inside an aggregate to count or total a subset.

Count rows that match

select count(*)                                          as total,
       sum(case when status = 'shipped' then 1 else 0 end) as shipped,
       count(*) filter (where status = 'shipped')          as shipped_v2
from   orders

Watch out

FILTER (WHERE ...) is a cleaner alternative toSUM(CASE ...) and works in DuckDB, Postgres, and SQLite, but not SQL Server or MySQL, where you need the SUM(CASE ...) form. Order your branches from most specific to most general, since the first match wins.

Lesson

Calculated Columns in SQL

Create derived values in a SELECT list.

Lesson

SQL CASE WHEN: Labeling Rows

Add if/else logic to query results.

Lesson

SQL COALESCE: Filling Gaps

Replace NULLs with useful defaults.

Lesson

Encoding Business Rules in SQL

Translate plain-language rules into CASE logic.