SQL CASE WHEN: Conditional Logic in Queries

Why CASE WHEN belongs in your toolkit

Spreadsheet users reach for IF the moment they need a label. In SQL the equivalent is CASE WHEN, and it runs on the server where the data already lives, so you ship a finished column instead of a raw export. On the Orders table you can turn a raw amount into a tier, a region into a territory, or a date into a season, all before the data reaches a dashboard.

The shape is always the same: evaluate conditions top to bottom, return the first match, and fall back to ELSE. I lean on it constantly because it keeps the logic next to the data instead of scattered across a report builder.

The basic CASE WHEN syntax

SELECT order_id, CASE WHEN amount > 500 THEN 'large' ELSE 'small' END AS size
FROM Orders

SELECT customer_id,
  CASE WHEN total
  > 5000 THEN 'whale'
  WHEN total > 1000
  THEN 'regular'
  ELSE 'new' END AS segment
FROM Customers

Labeling and bucketing values

SELECT product_id,
  CASE WHEN SUM(amount)
  > 10000 THEN 'A'
  WHEN SUM(amount)
  > 1000 THEN 'B'
  ELSE 'C' END AS tier
FROM Sales
GROUP BY product_id

SELECT region_code,
  CASE region_code
  WHEN 'W' THEN 'West'
  WHEN 'E' THEN 'East'
  WHEN 'S' THEN 'South'
  ELSE 'Other'
  END AS region
FROM Orders

SELECT order_id,
  CASE WHEN ship_date > due_date THEN 'late' ELSE 'on_time' END AS status
FROM Orders

Using CASE WHEN inside GROUP BY

SELECT CASE WHEN amount > 500 THEN 'large' ELSE 'small' END AS size,
  COUNT(order_id) AS orders
FROM Orders
GROUP BY CASE WHEN amount > 500 THEN 'large' ELSE 'small' END
ORDER BY orders DESC

SELECT CASE WHEN EXTRACT(month
  FROM order_date) <= 3 THEN 'Q1' ELSE 'later' END AS period, SUM(amount)
FROM Orders
GROUP BY CASE WHEN EXTRACT(month
  FROM order_date) <= 3 THEN 'Q1' ELSE 'later' END

Sorting with CASE WHEN in ORDER BY

SELECT region, revenue
FROM RegionSummary
ORDER BY CASE WHEN region = 'Total' THEN 0 ELSE 1 END, revenue DESC

The NULL trap that swallows rows

NULL breaks the intuition that a value is either true or false. Any comparison with NULL, such as amount > 500 when amount is NULL, returns unknown, not false, so the row falls through to ELSE. If you forgot ELSE, that row becomes NULL and disappears from a count or chart.

SELECT order_id,
  CASE WHEN amount IS NULL
  THEN 'missing'
  WHEN amount > 500
  THEN 'large'
  ELSE 'small' END AS size
FROM Orders

Worked example: customer value tier report

Here is a runnable query that labels each customer by total spend, then counts how many fall in each tier. It runs against the Orders and Customers tables.

SELECT c.customer_id,
  CASE WHEN SUM(o.amount)
  > 5000 THEN 'whale'
  WHEN SUM(o.amount)
  > 1000 THEN 'regular'
  ELSE 'new' END AS tier
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY tier;

SELECT tier, COUNT(*) AS customers
FROM (the query above) t
GROUP BY tier
ORDER BY customers DESC

A result of whale 320, regular 1840, new 560 tells you exactly how your base breaks down.

Nesting CASE WHEN for multi-rule logic

When one label depends on two columns, you can nest a CASE inside another. For example, tier customers by both region and spend over $5,000: CASE WHEN region = 'West' THEN CASE WHEN amount > 5000 THEN 'A' ELSE 'B' END ELSE 'C' END. The inner CASE only runs for the West branch, which keeps the logic readable instead of a flat list of every combination.

Nested CASE gets hard to read past two levels, so I recommend pulling the rule into a CTE or a short comment once it reaches three layers. A common mistake is forgetting the inner END, which leaves the expression unclosed and throws a syntax error that points to the wrong line, often the outer CASE rather than the missing close.

SELECT customer_id,
  CASE WHEN region
  = 'West' THEN CASE
  WHEN SUM(amount)
  > 5000 THEN 'A'
  ELSE 'B' END
  ELSE 'C' END AS tier
FROM Orders
GROUP BY customer_id, region
ORDER BY tier

CASE WHEN inside aggregates

CASE is not limited to SELECT labels. You can wrap it inside SUM, COUNT, or AVG to compute a conditional metric, which is the bridge to advanced aggregates. SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) adds only West rows, giving you a West revenue column sitting right next to the grand total in the same row.

This pattern removes the need for separate queries per segment. I recommend it whenever a stakeholder asks for 'revenue by region as columns' because one query returns the whole matrix instead of four. It is covered in depth in our advanced aggregate guide, but the building block is simply a CASE expression placed inside the aggregate function.

SELECT product_id,
  SUM(amount) AS total, SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west
FROM Orders
GROUP BY product_id
ORDER BY total DESC

Before you trust a CASE report, test the boundary values, because that is where labels break. A tier rule of 'more than 5000' excludes exactly 5000, so a customer who spent precisely $5,000 lands in the lower tier, and someone will notice. I keep a small table of test rows, including the exact boundary, one above, and one below, then confirm each gets the label I expect. This takes two minutes and catches the off-by-one that a quick glance at the big totals misses. In my experience boundary bugs are the most common reason a CASE-based segment disagrees with a hand count, and they are almost always invisible until a curious stakeholder checks a single account.

Next steps and a quick checklist

Write branches from most specific to least specific, always include an ELSE, and handle NULL before numeric tests. Use CASE in SELECT to label, in GROUP BY to bucket, and in ORDER BY to pin rows. Once comfortable, combine it with date aggregation to tag quarters as peak or off-season, or with advanced conditional aggregates to count only rows that meet a rule. Practice on the Orders table until the logic reads like plain English. Combine it with SQL GROUP BY and HAVING to group the labeled rows, and with SQL date aggregation to tag quarters as peak or off-season. For counting only rows that meet a rule, see the advanced aggregate guide.