SUM(CASE WHEN ...) as a Count

Concepts covered: Conditional Aggregation, Conditional Logic, Sums & Averages

CASE runs once per row, before any aggregate sees the row. CASE WHEN tier = 'premium' THEN 1 ELSE 0 END looks at one order and produces a 1 if the tier is premium and a 0 otherwise. SUM then adds those 1s and 0s across the group, and because every 0 adds nothing, the result is the number of premium orders. The row stayed in the group the whole time; the condition decided its contribution. That is the difference between this and a WHERE, and the interviewer will ask you to state it.

SELECT
  region,
  SUM(CASE WHEN tier = 'premium' THEN 1 ELSE 0 END) AS premium_orders,
  COUNT(*)                                            AS all_orders
FROM orders
GROUP BY region;

Watching a row become 1 or 0

The orders table below has 3 rows in North and 1 in South. The query adds a second conditional column that uses the same condition with amount in place of 1, so you can watch each row resolve to 1 or 0 for the count and to its amount or 0 for the revenue before the groups fold.

SELECT region,
  SUM(CASE WHEN tier = 'premium' THEN 1 ELSE 0 END) AS premium_orders,
  SUM(CASE WHEN tier = 'premium' THEN amount ELSE 0 END) AS premium_revenue,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY region

North holds orders 1 to 3. Order 1 is premium and becomes 1 and 100; order 2 is standard and becomes 0 and 0; order 3 is premium and becomes 1 and 200. The sums are 2 premium orders, 300 in premium revenue, and 350 in total revenue from the unconditional SUM(amount). South holds only order 4, a standard order at 300, so both of its CASE results are 0 and only the total column shows 300: a region with no premium orders still gets its row, with 0 in the premium columns. The standard orders contributed 0 to the premium columns, which is why the total column still sees their 50 and 300.

Same shape, a different value

The count form and the sum form are one pattern with the THEN swapped. THEN 1 counts matching rows; THEN amount sums their amounts; THEN amount * 0.1 would sum a derived value. When the interviewer adds 'and premium revenue' to a question about premium order counts, the new column is the same CASE with a different THEN. Saying so, 'same condition, the value changes from 1 to amount', shows you see one pattern rather than 2 separate tricks.

ELSE 0 or no ELSE

Dropping the ELSE makes non-matching rows produce NULL, and SUM skips NULL, so the count comes out the same on any group that has a match. The 2 forms differ in one case the interviewer likes to ask about: a region with no premium orders at all. With ELSE 0 that region's column is 0. With no ELSE, every row in the group produced NULL, and a SUM over nothing but NULL returns NULL. A dashboard tile showing a blank where the reader expects 0, or a downstream subtraction that turns into NULL, is what the follow-up is about. Write ELSE 0 inside SUM unless you have a reason to want the NULL, and say that reason.

SUM(CASE WHEN tier = 'premium' AND amount >= 100 THEN 1 ELSE 0 END)    AS big_premium_orders,
SUM(CASE WHEN region IN ('North', 'West') THEN amount ELSE 0 END)      AS northern_revenue,
SUM(CASE WHEN order_date >= DATE '2024-03-01' THEN 1 ELSE 0 END)       AS march_orders

The condition can use AND and OR to combine comparisons, and IN lists and date ranges work too, because CASE evaluates a boolean and nothing about SUM cares how that boolean was built. The slice gets narrower; the structure stays one column per slice, and a follow-up such as 'now only premium orders from this quarter' is an AND added to a condition rather than a new query.

About this lesson

This section is part of the Conditional Aggregation: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.