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 no ELSE 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 to SUM(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.

Reference

SQL CASE Expressions

Searched and simple CASE patterns.

Interview

SQL CTE Interview Questions

Practice WITH clauses and step-by-step reports.

Mistake

Why My Percentage Comes Out 0

Avoid truncating fractional results.