Y

SQL · Beginner

SQL CASE WHEN: conditional logic and buckets

10 min read

Why it matters

SQL CASE WHEN is the if-else of SQL, and analysts use it in nearly every report: turning codes into names, grouping ages or fares into bands, and counting several conditions in one pass. "Count orders by status in one row, one column per status" is one of the most common screening questions for analyst roles, and the answer is a CASE inside an aggregate.

The concept

Examples use taxi.duckdb from What is SQL? Databases, tables and your first query.

Two forms

Searched CASE tests any condition per branch:

sql
case when trip_distance = 0  then 'zero'
     when trip_distance < 1  then 'under 1 mile'
     else '1 mile or more'
end

Simple CASE compares one expression to values:

sql
case payment_type when 1 then 'Card' when 2 then 'Cash' else 'Other' end

Simple CASE uses = internally, so case payment_type when null then ... never matches: NULL = NULL is unknown. Handle NULL with a searched branch: when payment_type is null then 'Not recorded'.

The first true branch wins

Branches are checked top to bottom and the first TRUE one returns. Put narrow conditions before broad ones. when trip_distance > 2 then 'short' when trip_distance > 10 then 'long' never returns 'long', because anything over 10 is also over 2 and stops at the first branch.

Missing ELSE means NULL

If no branch matches and there is no else, CASE returns NULL. NULLs then disappear from count(col), form an anonymous group in group by, and sort to one end. Always write an else, even if it is else 'Other' or else null to show you meant it.

One result type

Every branch must return a compatible type. then 'card' else 0 mixes text and integer; PostgreSQL and DuckDB try to convert 'card' to an integer and fail. MySQL converts silently. Return the same type from every branch, casting numbers to text when you need a label.

Buckets and their order

Text labels sort alphabetically: '10 or more' sorts before '3 to 10'. Either prefix labels with a sort key ('1: under 1 mile'), or sort by a separate CASE expression, or keep the numeric lower bound as its own column and sort on that.

CASE inside aggregates

Aggregates plus CASE give conditional metrics in one scan:

MetricPattern
Count of matching rowssum(case when cond then 1 else 0 end) or count(case when cond then 1 end)
Share of matching rowsavg(case when cond then 1.0 else 0 end)
Sum over matching rowssum(case when cond then amount end)

count(case when cond then 1 end) works because the missing else returns NULL and count skips NULLs.

The FILTER clause

PostgreSQL, DuckDB and SQLite 3.30+ support the standard filter clause, which says the same thing more clearly:

sql
count(*) filter (where payment_type = 2)
sum(fare_amount) filter (where trip_distance > 10)

MySQL, SQL Server, BigQuery and Snowflake do not, so sum(case ...) is the portable form. In MySQL, comparisons return 1 or 0, so sum(payment_type = 2) also works there.

CASE in ORDER BY, and dialect shortcuts

order by case status when 'urgent' then 1 when 'normal' then 2 else 3 end sorts by business priority instead of alphabet. SQL Server has iif(cond, a, b) and MySQL has if(cond, a, b) as two-branch shortcuts; both are just CASE underneath and do not port.

Worked example: payment names, distance bands and an hourly summary

Label payment types

sql
select case payment_type
         when 1 then 'Card'
         when 2 then 'Cash'
         when 3 then 'No charge'
         when 4 then 'Dispute'
         else 'Not recorded'
       end as payment,
       count(*) as trips
from trips
group by all
order by trips desc;
text
┌──────────────┬────────┐
│   payment    │ trips  │
│   varchar    │ int64  │
├──────────────┼────────┤
│ Card         │ 934473 │
│ Cash         │ 322889 │
│ Not recorded │  98352 │
│ No charge    │   8384 │
│ Dispute      │   5667 │
└──────────────┴────────┘

The else caught the NULL payment types. A simple-CASE branch when null would not have:

sql
select case payment_type when null then 'null branch' else 'else branch' end as simple_case_null,
       count(*)
from trips where payment_type is null group by 1;
text
┌──────────────────┬──────────────┐
│ simple_case_null │ count_star() │
│     varchar      │    int64     │
├──────────────────┼──────────────┤
│ else branch      │        98352 │
└──────────────────┴──────────────┘

For real work, put payment names in a lookup table and join to it (the next module). CASE is right for a handful of stable codes and for logic, not for long lists that change.

Branch order matters

sql
select case when trip_distance > 2  then 'short'
            when trip_distance > 10 then 'long'
            else 'very short' end as band_wrong,
       count(*)
from trips group by all order by 2 desc;
text
┌────────────┬──────────────┐
│ band_wrong │ count_star() │
│  varchar   │    int64     │
├────────────┼──────────────┤
│ very short │       803688 │
│ short      │       566077 │
└────────────┴──────────────┘

No trip is ever labelled long. The 72,353 trips over 10 miles were all caught by the first branch.

Correct distance bands, in the right order

distance_bands.sql
select case when trip_distance = 0  then '0: zero'
            when trip_distance < 1  then '1: under 1 mile'
            when trip_distance < 3  then '2: 1 to 3 miles'
            when trip_distance < 10 then '3: 3 to 10 miles'
            else '4: 10 miles or more'
       end as band,
       count(*) as trips,
       round(avg(fare_amount), 2) as avg_fare
from trips
group by all
order by band;
text
┌─────────────────────┬────────┬──────────┐
│        band         │ trips  │ avg_fare │
│       varchar       │ int64  │  double  │
├─────────────────────┼────────┼──────────┤
│ 0: zero             │  19952 │    19.52 │
│ 1: under 1 mile     │ 311390 │     5.14 │
│ 2: 1 to 3 miles     │ 688605 │     8.78 │
│ 3: 3 to 10 miles    │ 277465 │    18.81 │
│ 4: 10 miles or more │  72353 │     45.8 │
└─────────────────────┴────────┴──────────┘

Read the result, not just the syntax. Zero-distance trips have an average fare of 19.52, higher than 3-to-10-mile trips. They are not tiny rides: they include flat-fare airport trips and meters that never recorded distance. That band deserves its own investigation before anyone computes "fare per mile".

The bands are mutually exclusive and cover every value because each branch only has to test the upper bound (the earlier branches already removed everything below). 19,952 + 311,390 + 688,605 + 277,465 + 72,353 = 1,369,765, the full table. Check that sum every time you bucket.

What a missing ELSE does

sql
select case when trip_distance < 1 then 'under 1'
            when trip_distance < 3 then '1 to 3' end as band,
       count(*)
from trips group by all order by 2 desc;
text
┌─────────┬──────────────┐
│  band   │ count_star() │
│ varchar │    int64     │
├─────────┼──────────────┤
│ 1 to 3  │       688605 │
│ NULL    │       349818 │
│ under 1 │       331342 │
└─────────┴──────────────┘

349,818 trips (everything 3 miles or longer) fell into an unnamed NULL group. In a chart, that bar is often silently dropped.

Mixed types fail

sql
select case when payment_type = 1 then 'card' else 0 end as label
from trips where payment_type = 1 limit 1;
text
Conversion Error: Could not convert string 'card' to INT32

PostgreSQL gives invalid input syntax for type integer: "card". Use else '0' or else 'other'.

One row per pickup hour with conditional shares

The practical question: how does payment mix and trip length change through the day?

hourly_mix.sql
select hour(tpep_pickup_datetime) as pickup_hour,
       count(*) as trips,
       round(100.0 * sum(case when payment_type = 1 then 1 else 0 end) / count(*), 1) as card_pct,
       round(100.0 * count(*) filter (where payment_type = 2) / count(*), 1)          as cash_pct,
       round(100.0 * avg(case when trip_distance > 10 then 1.0 else 0 end), 1)        as over_10mi_pct
from trips
where tpep_pickup_datetime >= '2021-01-01' and tpep_pickup_datetime < '2021-02-01'
group by pickup_hour
order by pickup_hour;
text
┌─────────────┬────────┬──────────┬──────────┬───────────────┐
│ pickup_hour │ trips  │ card_pct │ cash_pct │ over_10mi_pct │
│    int64    │ int64  │  double  │  double  │    double     │
├─────────────┼────────┼──────────┼──────────┼───────────────┤
│           0 │  12307 │     60.2 │     31.1 │          11.5 │
│           1 │   7987 │     58.0 │     33.1 │           9.0 │
│           2 │   4570 │     55.0 │     35.1 │           8.5 │
│           3 │   2775 │     49.0 │     36.2 │           8.5 │
│           4 │   3160 │     42.2 │     32.4 │          14.0 │
│           5 │   8088 │     47.9 │     30.1 │          18.4 │
│           6 │  24738 │     60.4 │     26.1 │           9.7 │
│           7 │  46003 │     67.0 │     21.9 │           5.4 │
│           8 │  69234 │     70.3 │     20.1 │           3.7 │
│           9 │  75060 │     67.5 │     22.6 │           3.6 │
│          10 │  82893 │     65.0 │     25.2 │           4.2 │
│          11 │  89785 │     65.2 │     25.6 │           3.9 │
│          12 │  98629 │     65.6 │     25.4 │           3.7 │
│          13 │ 104365 │     66.2 │     25.1 │           3.8 │
│          14 │ 112570 │     67.3 │     24.9 │           4.4 │
│          15 │ 113435 │     68.7 │     24.2 │           4.6 │
│          16 │ 106981 │     70.0 │     22.8 │           4.9 │
│          17 │ 104703 │     72.8 │     20.7 │           5.3 │
│          18 │  94676 │     74.1 │     20.0 │           4.7 │
│          19 │  70855 │     73.9 │     20.1 │           5.4 │
│          20 │  48475 │     71.3 │     22.0 │           7.6 │
│          21 │  37620 │     71.0 │     22.8 │           9.9 │
│          22 │  31408 │     68.4 │     24.8 │          10.6 │
│          23 │  19424 │     61.3 │     30.6 │          12.4 │
└─────────────┴────────┴──────────┴──────────┴───────────────┘

Three ways to write a conditional share, all in one query. What it tells you: card use peaks in the evening commute (74.1% at 18:00), cash is highest in the small hours, and the share of 10-mile-plus trips jumps at 04:00 to 05:00 (likely early airport runs). Note that card and cash do not add up to 100%: the remainder is no-charge, disputes and the NULL payment types. A good summary says so.

Status counts in one row

The interview classic, with payment type standing in for order status:

sql
select count(*) filter (where payment_type = 1)       as card,
       count(*) filter (where payment_type = 2)       as cash,
       count(*) filter (where payment_type = 3)       as no_charge,
       count(*) filter (where payment_type = 4)       as dispute,
       count(*) filter (where payment_type is null)   as not_recorded
from trips;
text
┌────────┬────────┬───────────┬─────────┬──────────────┐
│  card  │  cash  │ no_charge │ dispute │ not_recorded │
│ int64  │ int64  │   int64   │  int64  │    int64     │
├────────┼────────┼───────────┼─────────┼──────────────┤
│ 934473 │ 322889 │      8384 │    5667 │        98352 │
└────────┴────────┴───────────┴─────────┴──────────────┘

In MySQL or SQL Server, write each column as sum(case when payment_type = 1 then 1 else 0 end).

Custom sort order

Cash first, then card, then the rest:

sql
select payment_type, count(*) as trips
from trips group by payment_type
order by case payment_type when 2 then 1 when 1 then 2 else 3 end, payment_type;
text
┌──────────────┬────────┐
│ payment_type │ trips  │
│    int64     │ int64  │
├──────────────┼────────┤
│            2 │ 322889 │
│            1 │ 934473 │
│            3 │   8384 │
│            4 │   5667 │
│         NULL │  98352 │
└──────────────┴────────┘

The dialect shortcuts:

sql
-- SQL Server
select iif(2 > 1, 'yes', 'no') as iif_result;
-- MySQL
select if(2 > 1, 'yes', 'no') as if_result, sum(2 > 1) as bool_sum;
text
iif_result
----------
yes
 
if_result	bool_sum
yes	1

Common mistakes

  • Broad conditions first. > 2 before > 10 means the long band is always empty. Order from narrowest to broadest, or test ranges on both ends.

  • No ELSE. Unmatched rows become NULL and vanish from counts and charts. Always write one.

  • when null in a simple CASE. Never matches. Use a searched when col is null.

  • Integer division in shares. sum(case ... then 1 else 0 end) / count(*) is 0 in PostgreSQL and SQL Server. Multiply by 100.0 first or average a 1.0/0 flag.

  • Alphabetical band order. '10+' sorts before '3-10'. Prefix labels or sort by a separate key.

  • Bands that do not add up. Check that the band counts sum to the table count; gaps and overlaps show up immediately.

  • CASE for long code lists. Twenty when branches for product categories belong in a lookup table, so the next new category does not need a code change.

Interview angle

Count orders by status in one row, one column per status, without a PIVOT keyword.
sql
select sum(case when status = 'placed'    then 1 else 0 end) as placed,
       sum(case when status = 'shipped'   then 1 else 0 end) as shipped,
       sum(case when status = 'cancelled' then 1 else 0 end) as cancelled,
       sum(case when status is null       then 1 else 0 end) as unknown
from orders;

Mention that count(*) filter (where status = 'placed') is cleaner in PostgreSQL, and add a column for NULL or unexpected statuses so the columns add up to the total.

What does CASE return when no condition matches?

The else value, or NULL if there is no else. That NULL then drops out of count(col) and avg, which is useful on purpose (count(case when ... then 1 end)) and harmful by accident.

Is CASE evaluated lazily? Can it protect against division by zero?

Branches are checked in order and the database generally evaluates only the chosen result, so case when fare = 0 then null else tip / fare end is a common guard. But the standard does not promise it for every expression (constant folding and aggregates can be evaluated early in some engines), so tip / nullif(fare, 0) is the more reliable guard.

Practice task

On taxi.duckdb:

  1. Label each trip as airport if it starts or ends at zone 1, 132 or 138, manhattan if it starts in a Manhattan zone (use PULocationID in (select LocationID from zones where Borough = 'Manhattan')), otherwise other. Count trips and average fare per label.

  2. Build fare bands (under 10, 10 to 20, 20 to 50, 50 or more, plus a band for zero or negative fares), sorted correctly, and check they sum to the table.

  3. Make one row per day of week with trips, card share and share of trips with a tip over 20% of the fare (card trips only, using nullif).

Show a solution
sql
-- 1
select case when PULocationID in (1, 132, 138) or DOLocationID in (1, 132, 138) then 'airport'
            when PULocationID in (select LocationID from zones where Borough = 'Manhattan') then 'manhattan'
            else 'other' end as trip_type,
       count(*) as trips, round(avg(fare_amount), 2) as avg_fare
from trips group by all order by trips desc;

-- 2
select case when fare_amount <= 0 then '0: zero or negative'
            when fare_amount < 10 then '1: under 10'
            when fare_amount < 20 then '2: 10 to 20'
            when fare_amount < 50 then '3: 20 to 50'
            else '4: 50 or more' end as fare_band,
       count(*) as trips
from trips group by all order by fare_band;

-- 3
select dayname(tpep_pickup_datetime) as dow,
       count(*) as trips,
       round(100.0 * count(*) filter (where payment_type = 1) / count(*), 1) as card_pct,
       round(100.0 * avg(case when tip_amount / nullif(fare_amount, 0) > 0.2 then 1.0 else 0 end)
             filter (where payment_type = 1), 1) as big_tip_pct
from trips
group by all
order by min(isodow(tpep_pickup_datetime));

The airport branch comes first on purpose: a trip from Manhattan to JFK is an airport trip in this scheme. If your business wants it the other way, swap the branches and say so.

Further reading