Aggregate with CASE

Hook: This is the SQL version of sorting mail into different trays on one walk through the mailroom — interviewers love it because it tests whether you can do multiple summaries in one scan.

Question: What does it mean to use CASE inside an aggregate function in SQL?

Answer: It means you use CASE to turn each row into a value that an aggregate can count, sum, or average. This is called conditional aggregation, and it lets you answer questions like “How many paid orders do we have?” and “What is the total revenue from refunds?” in a single query.

Interview-Ready Answer: I use CASE inside aggregates when I want a conditional total without running separate queries. For example, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) adds only paid rows, while COUNT(CASE WHEN status = 'paid' THEN 1 END) counts only paid rows because non-matching rows become NULL and COUNT ignores NULL. The main benefit is that I can compute several metrics in one grouped scan.

What it is

Detailed Explanation: Aggregation means collapsing many rows into a smaller result, such as totals per day or counts per category. CASE is a conditional expression: it checks conditions row by row and returns one value if a condition matches, otherwise another value. When you put CASE inside SUM, COUNT, AVG, MAX, or MIN, you are telling SQL to aggregate only the rows that meet a rule.

How it works under the hood

1. SQL reads each row from the input set, either from a table scan or from rows already filtered by WHERE.
2. The CASE expression runs for that row and produces a value like 1, 0, an amount, or NULL.
3. The aggregate function consumes that value. For SUM, it adds numbers; for COUNT, it counts non-NULL values; for AVG, it keeps a running total and a running count.
4. If you group by a column, the database keeps one running aggregate per group, such as one bucket for each department or month.
5. At the end, SQL returns the final totals for each group or for the whole result set.

Why interviewers like this pattern

It shows you understand that SQL is good at set-based work. Instead of writing several passes over the same data, you can compute many business metrics at once: completed orders, canceled orders, high-value orders, and total revenue. That usually means cleaner SQL and fewer full table scans.

Common forms

• SUM(CASE WHEN condition THEN 1 ELSE 0 END) — counts rows that match.
• SUM(CASE WHEN condition THEN amount ELSE 0 END) — totals a numeric column only for matching rows.
• COUNT(CASE WHEN condition THEN 1 END) — counts matches because non-matches become NULL.
• AVG(CASE WHEN condition THEN amount END) — averages only matching rows, because AVG ignores NULL.

Comparison with alternatives

Note: Some databases support SUM(amount) FILTER (WHERE condition). That is often cleaner than CASE, but CASE is more portable because it works in almost every SQL database.

Performance and complexity

For a single grouped query, the work is usually O(n) over the number of input rows, because each row is inspected once. If you group by a column, the database may use a hash table or sort-based grouping; memory use depends on the number of groups, not just rows. In practice, one conditional aggregation query over 1 million rows is usually far cheaper than running 5 separate queries that each scan the same million rows.

Indexes help most when they let the database skip rows in a WHERE clause. But if you put the condition only inside CASE, the engine may still need to look at every row to evaluate it. So use WHERE when you want to reduce the input set, and use CASE when you want multiple conditional totals from the same input.

Important edge cases

• ELSE 0 vs no ELSE: For SUM, both can work, but for COUNT, using NULL is often the right trick because COUNT ignores NULL.
• Division by zero risk: If you later compute a rate like paid_orders / total_orders, guard the denominator with NULLIF(total_orders, 0).
• Null input values: SUM and AVG ignore NULL, so be deliberate about whether unmatched rows should become 0 or NULL.
• Overlapping conditions: If one row can satisfy multiple CASE expressions, it will contribute to multiple metrics. That is useful for dashboards, but dangerous if you expect categories to be mutually exclusive.

Memory hook

Think of CASE as a gatekeeper standing in front of the aggregate: “If you match, you get counted; if not, you become invisible.”

Real-World Story: Imagine a checkout service for an e-commerce app. The product team wants one dashboard query that shows total orders, paid orders, refunded orders, and revenue from only paid orders, all by day. A conditional aggregate query can produce those numbers in a single pass over the orders table.

The bug happens when a developer writes separate counts in separate queries and one of them uses the wrong date filter or status filter. The dashboard then shows “paid orders” higher than total orders, or the revenue number drops to zero for one day. Symptoms include confused support tickets, monitoring alerts from BI jobs, and logs showing queries that each return different row counts for the same date range. Users feel it as misleading reports, which can break trust in the metrics even if the checkout system itself is healthy.

In production, this matters because reporting queries often hit large tables. One correct conditional aggregation query is cheaper, easier to schedule, and less likely to drift than five separate ad hoc queries copied into different dashboards.

Follow-up & Tricky Questions: Interviewers often continue by testing whether you understand why the pattern works and where it breaks.

• How do you count only matching rows? Use SUM(CASE WHEN condition THEN 1 ELSE 0 END) or COUNT(CASE WHEN condition THEN 1 END). Both work, but the COUNT version relies on NULL being ignored.
• How do you sum only matching amounts? Use SUM(CASE WHEN condition THEN amount ELSE 0 END). That keeps the total numeric and avoids NULL in the final sum.
• How do you average only matching rows? Use AVG(CASE WHEN condition THEN amount END). Leaving out ELSE is useful here because non-matching rows become NULL and are skipped.
• When should you use WHERE instead? If you only need one metric and want to reduce rows early, use WHERE. CASE is best when you need several metrics from the same row set.
• Can multiple CASE expressions overlap? Yes. A row can contribute to more than one metric if your conditions overlap, so you must define business rules carefully.
• How do you calculate a conversion rate? Usually SUM(CASE WHEN converted THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0). NULLIF prevents division by zero.
• Do filtered aggregates replace this pattern? In databases that support them, SUM(amount) FILTER (WHERE status = 'paid') is often cleaner, but CASE remains more portable.

Tricky / gotcha questions:

• What is the difference between COUNT(CASE WHEN ... THEN 1 ELSE 0 END) and SUM(CASE WHEN ... THEN 1 ELSE 0 END)? COUNT counts both 1 and 0 because neither is NULL, so it does not count only matches. SUM is the correct tool for a 1/0 pattern.
• Why can AVG(CASE WHEN ... THEN amount ELSE 0 END) be wrong? Because the zeros from non-matching rows get included in the average and drag it down. Use ELSE NULL or omit ELSE so unmatched rows are skipped.
• Does SUM return NULL when nothing matches? Not if you used ELSE 0; it returns 0. But SUM(CASE WHEN ... THEN amount END) can return NULL if every row is unmatched, because there are no non-NULL values to sum.

Common Mistakes:

• Using COUNT(CASE ... THEN 1 ELSE 0 END) for matches. Correction: COUNT counts non-NULL values, so this counts every row. Use SUM(...) for 1/0 logic or omit ELSE and count only non-NULL matches.
• Putting ELSE 0 into an average. Correction: zeros from non-matches change the denominator and lower the average. Use AVG(CASE WHEN ... THEN value END) instead.
• Forgetting that conditions can overlap. Correction: if one row can match multiple categories, it will be counted multiple times. Make the rules mutually exclusive if the business meaning requires it.
• Using CASE when a WHERE filter would be simpler. Correction: filter early when you only need one metric; reserve conditional aggregation for multiple metrics or side-by-side comparisons.

Memory Hook: “CASE sorts, aggregate counts.” First, CASE decides what each row is worth; then the aggregate adds up the pile.

Cheat Sheet:

• SUM(CASE WHEN condition THEN 1 ELSE 0 END) = conditional count.
• SUM(CASE WHEN condition THEN amount ELSE 0 END) = conditional total.
• AVG(CASE WHEN condition THEN amount END) = conditional average.
• COUNT(CASE WHEN condition THEN 1 END) = count matches via non-NULL values.
• Use WHERE for one metric; use CASE for many metrics in one pass.
• ELSE NULL keeps unmatched rows out of AVG; ELSE 0 includes them as zeros.

Practice Tasks:

• Write a query that returns total users, active users, and inactive users from a users table.
• Modify the sample orders query to calculate paid revenue and refunded revenue by day.
• Add a conversion rate column that divides paid orders by total orders safely with NULLIF.