Conditional Aggregation

Hook: Conditional aggregation is the SQL version of sorting mail into labeled bins before you count it — one scan, many answers.

Question: What is conditional aggregation in SQL?

Answer: Conditional aggregation means using a condition inside an aggregate function so you can compute different totals from the same grouped rows. The usual pattern is CASE WHEN ... THEN ... END inside SUM, COUNT, or AVG. It is useful because you can produce several metrics in one query without running separate queries for each metric.

Interview-Ready Answer: I use conditional aggregation when I want multiple metrics from the same grouped data, like paid orders, refunded orders, and revenue in one pass. The core idea is to put a CASE expression inside an aggregate: for example, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) or COUNT(CASE WHEN status = 'paid' THEN 1 END). My main detail to mention is that COUNT ignores NULL, so the ELSE branch matters a lot — a wrong ELSE 0 can make the count incorrect.

What it is

Conditional aggregation is a way to make one grouped query answer several business questions at once. The CASE expression acts like a tiny per-row rule: if the row matches the condition, it contributes a value; otherwise it contributes NULL or 0, depending on the metric you want.

How it works under the hood

1. Rows are filtered first with WHERE. Any row removed here never reaches the aggregate, so this step changes both totals and denominators.
2. Rows are grouped by GROUP BY. Think of this as putting rows into buckets such as one bucket per customer, country, or day.
3. CASE is evaluated for each row in each bucket. A CASE expression is just a rule engine that returns a value row by row.
4. The aggregate folds the values together. SUM adds numbers, COUNT(expr) counts non-NULL values, and AVG averages only the non-NULL values.
5. The final result has one row per group. That is why conditional aggregation is ideal for dashboards and reporting queries.

Why the ELSE branch matters

For COUNT(CASE WHEN condition THEN 1 END), non-matching rows become NULL and are ignored, so the count is correct. For SUM(CASE WHEN condition THEN amount ELSE 0 END), non-matching rows contribute zero, so the total stays numeric even if nothing matches. But SUM(CASE WHEN condition THEN amount END) can return NULL when no row matches, because the aggregate only sees NULL values.

Comparison with other approaches

FILTER is part of the SQL standard and reads nicely, for example SUM(amount) FILTER (WHERE status = 'paid'). But CASE works in more databases, so interviewers often expect that form first.

When and why to use it

• When you need several counts or sums from the same table.
• When you want one grouped result set instead of many separate queries.
• When you need a portable pattern that works across most SQL engines.

Performance and complexity

In practice, conditional aggregation is usually a single pass over the input rows, so the scan cost is roughly O(n) for n rows, plus grouping overhead. If the database uses a hash aggregate, memory is roughly O(g) for g groups; if it uses a sort aggregate, you may also pay for sorting. A very practical win is that one query with three conditional metrics can avoid three separate table scans, which matters a lot on tables with millions of rows and noticeable disk I/O.

Important edge cases

• No matching rows: COUNT gives 0, but SUM may give NULL unless you use ELSE 0 or COALESCE.
• Overlapping conditions: the same row can be counted in more than one bucket if your rules overlap.
• Wrong denominator: if you filter in WHERE, you may accidentally shrink the total you wanted to compare against.
• COUNT trap: COUNT ignores only NULL, so returning 0 in the false branch makes the row count, which is usually wrong.

Memory hook: Label first, tally second. CASE labels each row; the aggregate only counts the labeled result.

Real-world story

Imagine a checkout service for an e-commerce platform. Every minute, the analytics job needs to report paid orders, refunded orders, pending orders, and paid revenue per merchant. Conditional aggregation lets the team compute all of that from the same orders table in one grouped query, which keeps the dashboard fast and consistent.

What goes wrong when someone misunderstands it? A developer writes COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) thinking the ELSE 0 means false, but COUNT counts every non-NULL value, so every row gets counted as paid. The symptom is a dashboard where paid orders suddenly equal total orders for every merchant. There is no SQL error, just suspiciously perfect metrics, and the business may make bad decisions because the conversion rate looks artificially high.

How it shows up: the logs look normal, the query finishes fast, but the numbers do not match the raw order totals. SREs and analysts usually spot it when the paid count becomes identical to the total count or when refunds appear to exceed the number of successful orders.

Follow-up & Tricky Questions:

• How is conditional aggregation different from filtering with WHERE? WHERE removes rows before grouping, so it changes the population you are measuring. Conditional aggregation keeps the row in the group and only changes how that row contributes to one metric.
• When should I use FILTER (WHERE ...) instead of CASE? Use FILTER when your database supports it and you want cleaner syntax. Use CASE when you want maximum portability across SQL engines.
• How do I count rows that match a condition? The classic pattern is COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN 1 ELSE 0 END). Both work, but the COUNT version is often easier to read for pure counts.
• How do I calculate a percentage with conditional aggregation? Divide the conditional count by the total count, and protect the denominator with NULLIF(total, 0) so you do not divide by zero.
• Can I compute many buckets in one query? Yes — that is one of the main strengths of this pattern. You can add one CASE expression per metric, such as paid, refunded, pending, and cancelled.
• Why is COUNT(CASE WHEN ... THEN 1 ELSE 0 END) a trap? Because COUNT counts any non-NULL value, and 0 is still a value. That means every row is counted, even the ones that fail the condition.
• Why can SUM(CASE WHEN ... THEN amount END) return NULL? If nothing matches, the CASE returns only NULL values, and SUM over all-NULL input returns NULL. Use ELSE 0 or wrap the result in COALESCE.
• Does the CASE clause make the database read fewer rows? No. It changes values after rows are read. If you want fewer rows scanned, use a real filter in WHERE or pre-filter in a subquery.

Common Mistakes:

• Using COUNT(... ELSE 0): correction — return NULL in the false branch, or omit ELSE entirely, so only matching rows are counted.
• Forgetting the ELSE 0 for sums: correction — use ELSE 0 or COALESCE when you need a zero instead of NULL.
• Mixing up the denominator: correction — if you need a rate, make sure the total count includes the rows you actually want as the base.
• Overlapping rules: correction — define categories so each row belongs to one bucket, or say clearly that double counting is intentional.

Memory Hook: Label first, tally second. The CASE expression puts each row into a bucket; the aggregate counts the bucket contents.

Cheat Sheet:

• SUM(CASE WHEN condition THEN value ELSE 0 END) for safe conditional totals.
• COUNT(CASE WHEN condition THEN 1 END) for conditional row counts.
• AVG(CASE WHEN condition THEN value END) averages only matching rows.
• COUNT ignores NULL; SUM ignores NULL but may return NULL if nothing matches.
• FILTER (WHERE ...) is cleaner when your DB supports it, but CASE is more portable.
• One query can produce many metrics from the same grouped data.

Practice Tasks:

• Write a query that counts paid, refunded, and pending orders per customer.
• Add a refund rate column using conditional aggregation and NULLIF.
• Rewrite the same report with FILTER (WHERE ...) if your database supports it.