SQL · Guides · October 30, 2026 · 13 min read

SQL CASE WHEN: conditional logic, categories and conditional aggregation

CASE is SQL’s if-then-else. It looks at each row, checks conditions in order, and returns the value of the first condition that is true. If none is true, it returns the ELSE value, or NULL if there is no ELSE:

CASE
  WHEN condition_1 THEN value_1
  WHEN condition_2 THEN value_2
  ELSE fallback_value
END

CASE is an expression, not a statement, so it can go anywhere a value can: in SELECT, WHERE, GROUP BY, ORDER BY, and inside aggregate functions. That last use, conditional aggregation, is where CASE does its most valuable work for analysts.

The examples run on the airline database from the Nomto SQL course: 33,121 flights between 104 airports in summer 2026, with bookings, tickets, seats and a loyalty programme. Money is in US dollars. The SQL is PostgreSQL syntax, and every result was produced by running the query.

Searched CASE: any condition

The common form, called searched CASE, takes a full condition after each WHEN. Sorting the fleet into range categories:

SELECT model, range,
       CASE
         WHEN range >= 6000 THEN 'long-haul'
         WHEN range >= 3000 THEN 'medium-haul'
         ELSE 'short-haul'
       END AS category
FROM aircrafts
ORDER BY range DESC;

Notice that the second branch says only range >= 3000, not range >= 3000 AND range < 6000. It does not need the upper bound, because rows with 6,000 or more have already been caught by the first branch. That is the rule that makes CASE work: branches are checked top to bottom, and the first match wins.

Simple CASE: compare one expression with values

When every branch compares the same expression with a constant, the simple form is shorter:

SELECT aircraft_code, model,
       CASE aircraft_code
         WHEN 'SU9' THEN 'Sukhoi'
         WHEN 'CR2' THEN 'Bombardier'
         WHEN 'CN1' THEN 'Cessna'
         ELSE split_part(model, ' ', 1)
       END AS maker
FROM aircrafts
ORDER BY aircraft_code;

CASE aircraft_code WHEN 'SU9' means CASE WHEN aircraft_code = 'SU9'. The values can be expressions too: the ELSE here takes the first word of the model name. (Here every branch happens to give the first word anyway; in real data the explicit branches are where the exceptions go.) The simple form can only test equality, so for ranges, LIKE, IN or NULL checks you need the searched form.

Bucketing numbers into categories

Turning a continuous number into bands is the everyday job of CASE. How are segment prices distributed?

SELECT CASE
         WHEN amount < 100 THEN 'under $100'
         WHEN amount < 300 THEN '$100-299'
         WHEN amount < 600 THEN '$300-599'
         ELSE '$600+'
       END AS price_band,
       COUNT(*) AS segments
FROM ticket_flights
GROUP BY price_band
ORDER BY MIN(amount);

Two small tricks here. Grouping by the alias price_band works in PostgreSQL and DuckDB (strict standard SQL would make you repeat the whole CASE or use its position, GROUP BY 1). And ORDER BY MIN(amount) sorts the bands by their real order instead of alphabetically, where $100-299 would come before under $100.

The same idea works on time intervals. How late were arrived flights at departure?

SELECT CASE
         WHEN actual_departure - scheduled_departure <= INTERVAL '5 minutes' THEN 'up to 5 min'
         WHEN actual_departure - scheduled_departure <= INTERVAL '15 minutes' THEN '6-15 min'
         WHEN actual_departure - scheduled_departure <= INTERVAL '3 hours' THEN '15 min to 3 h'
         ELSE 'over 3 h'
       END AS delay_group,
       COUNT(*) AS flights
FROM flights
WHERE status = 'Arrived'
GROUP BY delay_group
ORDER BY MIN(actual_departure - scheduled_departure);

First match wins: the order of branches matters

Put the branches in the wrong order and a category silently disappears:

SELECT CASE
         WHEN actual_departure - scheduled_departure > INTERVAL '15 minutes' THEN 'late'
         WHEN actual_departure - scheduled_departure > INTERVAL '3 hours' THEN 'very late'
         ELSE 'on time'
       END AS delay_group,
       COUNT(*) AS flights
FROM flights
WHERE status = 'Arrived'
GROUP BY delay_group
ORDER BY flights DESC;

Every flight more than three hours late is also more than 15 minutes late, so the first branch takes it and very late never fires. No error, no warning, just a category with zero rows that nobody notices is missing. With “greater than” conditions, put the largest threshold first; with “less than”, put the smallest first, as in the price bands above.

CASE and NULL

Two NULL rules catch people out with CASE.

A condition that is NULL counts as false, so the row falls through to ELSE. In the loyalty programme, birth_year is missing for some members. Watch what this does:

SELECT CASE
         WHEN birth_year < 1980 THEN 'born before 1980'
         ELSE 'born 1980 or later'
       END AS age_group,
       COUNT(*) AS members
FROM loyalty_members
GROUP BY age_group
ORDER BY age_group;

The 6,319 members with no birth year were labelled “born 1980 or later”. Handle NULL in its own branch, first:

SELECT CASE
         WHEN birth_year IS NULL THEN 'unknown'
         WHEN birth_year < 1980 THEN 'born before 1980'
         ELSE 'born 1980 or later'
       END AS age_group,
       COUNT(*) AS members
FROM loyalty_members
GROUP BY age_group
ORDER BY age_group;

Simple CASE cannot match NULL. CASE birth_year WHEN NULL THEN 'unknown' ELSE 'known' END labels all 53,000 members “known”, because it tests birth_year = NULL, which is never true. Use WHEN birth_year IS NULL in the searched form.

And if you leave out ELSE, unmatched rows get NULL:

SELECT model,
       CASE
         WHEN range >= 6000 THEN 'long-haul'
         WHEN range >= 3000 THEN 'medium-haul'
       END AS category
FROM aircrafts
WHERE range < 3000;

Sometimes that is exactly what you want (it is the basis of the AVG trick below). When it is not, an explicit ELSE 'other' makes gaps visible. More on NULL behaviour in the guide to NULL in SQL.

CASE in ORDER BY: a custom sort order

Alphabetical order rarely matches business order. Flight statuses have a natural life cycle:

SELECT status, COUNT(*) AS flights
FROM flights
GROUP BY status
ORDER BY CASE status
           WHEN 'Scheduled' THEN 1
           WHEN 'On Time' THEN 2
           WHEN 'Delayed' THEN 3
           WHEN 'Departed' THEN 4
           WHEN 'Arrived' THEN 5
           ELSE 6
         END;

The CASE returns a number used only for sorting; it does not appear in the output. The ELSE 6 sends anything unexpected, here Cancelled, to the end instead of breaking the order.

CASE in GROUP BY: your own segments

You can group by a CASE to create segments that do not exist as a column. Do weekends have more flights per day than weekdays?

SELECT CASE
         WHEN EXTRACT(ISODOW FROM scheduled_departure) IN (6, 7) THEN 'weekend'
         ELSE 'weekday'
       END AS day_type,
       COUNT(*) AS flights,
       COUNT(DISTINCT CAST(scheduled_departure AS date)) AS days,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT CAST(scheduled_departure AS date)), 1) AS flights_per_day
FROM flights
GROUP BY day_type
ORDER BY day_type;

Comparing raw totals (23,555 against 9,566) would be meaningless, since there are far more weekdays. Dividing by the number of days in each segment gives a fair comparison: weekends are about 5% busier.

Conditional aggregation

Put a CASE inside an aggregate and you can compute several filtered metrics in one pass over the table, one per column. This is the most useful CASE pattern in analytics.

Counting with SUM(CASE … THEN 1 ELSE 0 END)

How many flights did each big airport cancel, and what share is that?

SELECT departure_airport,
       COUNT(*) AS flights,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(100.0 * SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancel_pct
FROM flights
WHERE departure_airport IN ('SVO', 'DME', 'VKO', 'LED')
GROUP BY departure_airport
ORDER BY departure_airport;

A WHERE status = 'Cancelled' could not do this: it would throw away the other flights, and the denominator with them. Conditional aggregation keeps all rows and counts only the ones you care about. Note the 100.0: with 100 and integer counts, PostgreSQL would do integer division and return 0. DuckDB’s / always returns a fractional result, so the same bug stays hidden there until the query runs on PostgreSQL; DuckDB uses // for integer division.

There are three equivalent ways to count conditionally, and one tempting wrong one:

SELECT COUNT(*) AS arrived,
       SUM(CASE WHEN actual_departure - scheduled_departure > INTERVAL '15 minutes' THEN 1 ELSE 0 END) AS late,
       COUNT(CASE WHEN actual_departure - scheduled_departure > INTERVAL '15 minutes' THEN 1 END) AS late_count,
       ROUND(AVG(CASE WHEN actual_departure - scheduled_departure > INTERVAL '15 minutes' THEN 1.0 ELSE 0 END) * 100, 2) AS late_pct
FROM flights
WHERE status = 'Arrived';

• SUM(CASE ... THEN 1 ELSE 0 END) adds a 1 for each match.
• COUNT(CASE ... THEN 1 END) works because COUNT(expression) skips NULLs, and with no ELSE the non-matching rows are NULL.
• AVG(CASE ... THEN 1.0 ELSE 0 END) gives the share directly: the average of ones and zeros is the proportion of ones.

The wrong one is COUNT(CASE ... THEN 1 ELSE 0 END). Zero is not NULL, so COUNT counts it, and you get 16,707: every row. If you use COUNT, leave out the ELSE; if you use SUM, keep ELSE 0.

FILTER: the cleaner syntax

PostgreSQL and DuckDB support the standard FILTER clause, which says the same thing more directly. A seat map per aircraft, one column per class:

SELECT aircraft_code,
       COUNT(*) FILTER (WHERE fare_conditions = 'Economy') AS economy,
       COUNT(*) FILTER (WHERE fare_conditions = 'Comfort') AS comfort,
       COUNT(*) FILTER (WHERE fare_conditions = 'Business') AS business
FROM seats
GROUP BY aircraft_code
ORDER BY aircraft_code;

This is a pivot: rows of fare_conditions turned into columns. FILTER is not available in MySQL, SQL Server or Oracle, so if your code has to run there, use SUM(CASE ...); it works everywhere.

Summing amounts, not just counting

CASE inside SUM can return the value itself instead of 1. Monthly revenue of arrived flights, split by class:

SELECT date_trunc('month', f.scheduled_departure)::date AS month,
       ROUND(SUM(CASE WHEN tf.fare_conditions = 'Economy' THEN tf.amount ELSE 0 END)) AS economy,
       ROUND(SUM(CASE WHEN tf.fare_conditions = 'Comfort' THEN tf.amount ELSE 0 END)) AS comfort,
       ROUND(SUM(CASE WHEN tf.fare_conditions = 'Business' THEN tf.amount ELSE 0 END)) AS business
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
WHERE f.status = 'Arrived'
GROUP BY month
ORDER BY month;

(August is lower because the data snapshot ends on 11 August; the rest of the month has not flown yet.)

And with no ELSE, AVG only averages the matching rows, because the others are NULL and aggregates skip NULLs:

SELECT fare_conditions,
       ROUND(SUM(amount)) AS revenue,
       ROUND(SUM(CASE WHEN amount >= 600 THEN amount ELSE 0 END)) AS revenue_600plus,
       ROUND(AVG(CASE WHEN amount >= 600 THEN amount END), 2) AS avg_600plus
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY revenue DESC;

Here ELSE 0 and no ELSE give different, both correct, results: the SUM needs zeros so the total is 0 rather than NULL, while the AVG must not include zeros, or it would average over every segment. Comfort has no segments at $600 or more, so its average is NULL: there is nothing to average.

Types: every branch must return the same type

All THEN and ELSE values must fit one data type. CASE WHEN ... THEN 1 ELSE 'none' END mixes an integer with text. PostgreSQL rejects it with “invalid input syntax for type integer”. DuckDB fails too, with a conversion error, but only once a row actually reaches the 'none' branch, so a query can pass a test on a small sample and break later. Cast explicitly: THEN CAST(x AS VARCHAR), or return NULL instead of a placeholder string.

CASE in WHERE: usually not needed

You can write WHERE CASE WHEN ... THEN ... END, but a filter almost always reads better as plain AND / OR logic. WHERE CASE WHEN status = 'Arrived' THEN actual_departure > '2026-08-01' ELSE false END is just WHERE status = 'Arrived' AND actual_departure > '2026-08-01'. Plain conditions are also easier for the optimizer to use with indexes.

Related shortcuts

• COALESCE(a, b) is CASE WHEN a IS NOT NULL THEN a ELSE b END.
• NULLIF(a, b) is CASE WHEN a = b THEN NULL ELSE a END; its main use is avoiding division by zero.
• MySQL has IF(cond, a, b) and SQL Server has IIF(cond, a, b). They are shorter but not portable; CASE runs everywhere.

For more GROUP BY patterns that use conditional counts, see GROUP BY vs HAVING. Pivots with conditional aggregation are also a regular item in SQL interview questions.

FAQ

What is the difference between simple and searched CASE?

Simple CASE x WHEN 1 THEN ... compares one expression with values for equality. Searched CASE WHEN condition THEN ... takes any condition: ranges, LIKE, IN, IS NULL, combinations with AND. The searched form can do everything the simple one can.

What does CASE return if no condition matches?

The ELSE value, or NULL if there is no ELSE.

Can I use CASE in a WHERE clause?

Yes, but it is rarely the clearest option. Most CASE expressions in WHERE can be rewritten as ordinary AND / OR conditions.

How do I count rows that meet a condition in SQL?

Use conditional aggregation: SUM(CASE WHEN condition THEN 1 ELSE 0 END), COUNT(CASE WHEN condition THEN 1 END), or in PostgreSQL and DuckDB COUNT(*) FILTER (WHERE condition). All three keep the other rows available for totals and percentages.

Can I nest CASE expressions?

Yes, a THEN or ELSE can contain another CASE. Usually, though, a single CASE with conditions combined by AND reads better than nesting.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文