BeginnerSQL · 25 min

Conditional Aggregation: Beginner

Conditional aggregation is how SQL turns a long table into a wide dashboard row. Revenue by segment, count of active vs inactive users, premium vs standard subscribers, mobile vs web sessions, conversion vs non-conversion, all in one row of output. The technique is one expression: SUM, COUNT, or AVG wrapped around a CASE WHEN. Every DE interview asks some version of this, often as the second question after a basic GROUP BY. The candidates who reach for it without thinking are the candidates who have built dashboards; the candidates who fall back to multiple separate queries with UNION are the candidates who have not.

Spot the pivot opportunity: 'one row with multiple slice columns' is conditional aggregation

Write SUM(CASE WHEN ... THEN value END) and COUNT(CASE WHEN ... THEN 1 END) from memory

Understand why WHERE filtering and CASE inside aggregates produce different shapes

Reach for the FILTER clause on engines that support it for cleaner syntax

When the Output Is a Pivot-Like Summary

Daily Life

Interviews

Spot questions that need multiple metrics from a single pass: "count of X where status=A, count where status=B."

Here is the question every interview asks once you can do basic GROUP BY: 'show me one row per region, with separate columns for active customer count, inactive customer count, and total revenue, all from the same customers table.' The candidate who writes three separate queries and UNIONs them produces something that works but is unreadable. The candidate who writes 'SELECT region, COUNT(CASE WHEN status='active' THEN 1 END) AS active, ..., SUM(amount) AS revenue FROM customers GROUP BY region' produces a single readable query. The trick is the CASE inside the aggregate. That single pattern is the foundation of every KPI dashboard query in production data engineering.

You are being tested on conditional aggregation when you hear:

▸"one row per group, with separate columns for each segment"
▸"count active customers and total customers in the same row"
▸"pivot this long table into a wide one"
▸"revenue by region, split into premium and standard tiers"
▸"conversion rate per cohort with the numerator and denominator visible"
▸Any question where the output has columns that count or sum subsets of the input

What conditional aggregation actually does

A normal aggregate (SUM, COUNT, AVG) operates on every row in the group. A conditional aggregate operates on every row in the group but only counts the ones matching the CASE condition. SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) counts active rows. SUM(CASE WHEN status='active' THEN amount ELSE 0 END) sums amounts from active rows. The CASE inside the aggregate filters what contributes to the sum, while the row itself stays in the group. This is what lets one query produce multiple slice columns in the same row.

Spotting the pattern in the first ten seconds

Read the question. If the output has 'count of X' and 'count of Y' and 'sum of Z' all in the same row of output, you need conditional aggregation. The trigger words: 'broken down by,' 'split into,' 'with separate columns for,' 'one row per group with metrics for each subset.' Say one sentence before writing SQL: 'I'll GROUP BY the grouping column and use conditional aggregates (SUM with CASE WHEN) to produce one column per slice. The slices are X, Y, and the overall.' That sentence covers the pattern and the structure. Saying it tells the interviewer you saw the pivot shape immediately.

The ten-second sentence to say before writing SQL:

▸"This is conditional aggregation."
▸"One GROUP BY with conditional aggregates per slice."
▸"SUM(CASE WHEN ... THEN amount END) for sums; COUNT(CASE WHEN ... THEN 1 END) for counts."
▸"One row per group with multiple slice columns."

•Weak opening

Writes three separate SELECT queries (active, inactive, revenue) and UNIONs them
Returns three rows per region instead of one
Has to be corrected when the interviewer asks for one row per group
Wastes time recovering from a wrong query shape

✓Strong opening

Says "this is conditional aggregation, one GROUP BY with multiple CASE-wrapped aggregates"
Writes a single query with SUM(CASE...) and COUNT(CASE...) expressions
Produces one row per group with multiple slice columns
Has working SQL on the page within ninety seconds

Why companies care

Conditional aggregation powers every operational dashboard. The KPI tile that shows 'active users: 12,000, new users: 3,500, churned users: 800' is a conditional aggregation. The funnel chart that shows the count at each step is a series of conditional COUNTs. The revenue-by-segment breakdown that puts premium and standard side by side in one row is conditional SUM. If you cannot write this pattern, the dashboards you build are slow (multiple queries instead of one) and ugly (data in three rows that should be in one). That is why this question shows up in every entry-level DE interview.

SUM(CASE WHEN ... THEN 1 ELSE 0 END)

Daily Life

Interviews

Write conditional aggregation patterns for counts, sums, averages, and ratios in a single GROUP BY.

The fundamental form is SUM(CASE WHEN condition THEN value ELSE 0 END). The CASE returns the value when the condition is TRUE, and 0 otherwise. SUM adds them all up. The result is the sum of values from matching rows; non-matching rows contribute 0 and do not affect the total. The pattern works because SUM ignores NULL and treats 0 as additive identity; both routes (NULL or 0) give the right answer.

sum of values from matching rows. Non-matching contribute 0.
same result; non-matching return NULL which SUM ignores. Either ELSE or no ELSE works.
the unsliced total in the same row; lets the consumer reconcile the slices against the whole.

The canonical query

	/* Revenue split by tier, with total, one row per region */
	SELECT
	region,
	SUM(
	CASE
	WHEN tier = 'premium' THEN amount
	ELSE 0
	END
	) AS premium_revenue,
	SUM(
	CASE
	WHEN tier = 'standard' THEN amount
	ELSE 0
	END
	) AS standard_revenue,
	SUM(amount) AS total_revenue,
	COUNT(*) AS order_count
	FROM orders
	GROUP BY region
	ORDER BY total_revenue DESC

Reading the pattern

GROUP BY region collapses the orders to one row per region. Each conditional SUM filters which orders contribute: premium_revenue sums only the premium-tier orders, standard_revenue sums only standard-tier orders. total_revenue sums all orders unconditionally. order_count counts all orders unconditionally. Five columns in the output: the grouping key plus four metrics, three of them sliced. Each row of output has all four metrics for one region; the dashboard renders them side by side.

The ELSE 0 vs ELSE NULL choice

Two ways to write the CASE: ELSE 0 (the row contributes 0 to the sum) or ELSE NULL or no ELSE at all (the row contributes NULL, which SUM ignores). Both produce the same SUM result. The ELSE 0 form is more explicit about intent and reads as 'rows that don't match contribute zero to the sum.' The ELSE NULL form is slightly more efficient on some engines because SUM can skip NULL rows without summing zeros, but the difference is negligible at any normal scale. Pick the form your team uses; ELSE 0 is more common in tutorials and reads more explicitly to anyone new to the pattern.

SUM(CASE WHEN tier = 'premium' THEN amount ELSE 0 END) SUM(CASE WHEN tier = 'premium' THEN amount END)

Walk through a small example

Suppose orders has six rows. North region: (premium, $100), (premium, $200), (standard, $50). South region: (standard, $300), (standard, $400), (premium, $150). After GROUP BY region, two rows are produced. For North: premium_revenue is SUM of $100 + $200 + 0 = $300. standard_revenue is SUM of 0 + 0 + $50 = $50. total_revenue is $350. For South: premium_revenue is $150; standard_revenue is $700; total_revenue is $850. Two rows of output, six metrics each. The dashboard renders both regions side by side.

region	premium_revenue	standard_revenue	total_revenue	order_count
North	300	50	350	3
South	150	700	850	3

Multiple conditions in one CASE

The CASE can have multiple WHEN branches if you need to slice on multiple values. SUM(CASE WHEN tier = 'premium' AND region = 'US' THEN amount ELSE 0 END) sums premium US orders. The condition can be arbitrary SQL boolean expressions; combine with AND, OR, NOT, comparisons, IN clauses. The result is still one column per slice; just the slice condition is more specific.

TIP

Memorize the SUM(CASE WHEN ... THEN amount END) shape. You will write it in every other SQL interview. Practice the variant where the value is 1 (for counting) and the variant where the value is the column itself (for summing); both are the same pattern with different value expressions. The candidate who fluently produces this pattern is the candidate the interviewer reads as having built dashboards before.

Counting Subsets Without Extra Queries

Daily Life

Interviews

Use the FILTER clause (Postgres/DuckDB) as a cleaner alternative and know which dialects support it.

COUNT has a quirk that makes the conditional pattern slightly different from SUM. COUNT(*) counts every row in the group. COUNT(column) counts rows where column is not NULL. COUNT(CASE WHEN condition THEN 1 END) counts rows where the condition is TRUE (because non-matching rows return NULL, which COUNT skips). The 1 in the THEN is a sentinel; any non-NULL value works (COUNT does not care about the value, only that it is non-NULL).

Why COUNT works differently from SUM:

▸COUNT(*) counts every row in the group
▸COUNT(column) skips rows where column is NULL
▸COUNT(CASE WHEN cond THEN 1 END) skips non-matching rows (they return NULL)
▸Adding ELSE 0 to the COUNT CASE would count every row; convention is no ELSE

The canonical COUNT pattern

	/* Customer status breakdown per region, one row each */
	SELECT
	region,
	COUNT(*) AS total_customers,
	COUNT(
	CASE
	WHEN status = 'active' THEN 1
	END
	) AS active_count,
	COUNT(
	CASE
	WHEN status = 'inactive' THEN 1
	END
	) AS inactive_count,
	COUNT(
	CASE
	WHEN status = 'churned' THEN 1
	END
	) AS churned_count
	FROM customers
	GROUP BY region

Why no ELSE in the COUNT version

In COUNT(CASE WHEN ... THEN 1 END), the absence of an ELSE means non-matching rows return NULL. COUNT skips NULL, so non-matching rows do not contribute. Adding ELSE 0 would make COUNT count those rows too (because 0 is not NULL), which would make every conditional COUNT equal to COUNT(*). The convention is: SUM uses ELSE 0 to be explicit; COUNT omits ELSE because the NULL behavior is exactly what you want. State this when writing: 'SUM with ELSE 0; COUNT without ELSE because NULL is what makes the filter work.'

COUNT DISTINCT with CASE for filtered distinct counts

A common variant: count of distinct active users per region. COUNT(DISTINCT CASE WHEN status = 'active' THEN user_id END) counts unique user_ids from active rows only. Non-active rows return NULL, which COUNT DISTINCT skips. This is the cleanest way to count distinct values from a filtered subset within a GROUP BY.

	/* Distinct active users per region */
	SELECT
	region,
	COUNT(DISTINCT user_id) AS total_unique_users,
	COUNT(DISTINCT
	CASE
	WHEN status = 'active' THEN user_id
	END
	) AS active_unique_users,
	COUNT(DISTINCT
	CASE
	WHEN status = 'churned' THEN user_id
	END
	) AS churned_unique_users
	FROM customer_events
	GROUP BY region

The conversion-rate pattern

Conditional aggregation produces both the numerator and denominator of conversion-style metrics in one row. The conversion rate is then a simple ratio.

	/* Conversion rate per cohort: signups who completed onboarding */
	SELECT
	cohort_month,
	COUNT(*) AS signups,
	COUNT(
	CASE
	WHEN completed_onboarding = TRUE THEN 1
	END
	) AS converters,
	COUNT(
	CASE
	WHEN completed_onboarding = TRUE THEN 1
	END
	) * 1 / NULLIF(COUNT(*), 0) AS conversion_rate
	FROM signups
	GROUP BY cohort_month
	ORDER BY cohort_month

The conversion_rate column is the ratio of converters to total signups. NULLIF on the denominator prevents division by zero for cohorts with no signups (which should not happen but defensively belongs in the query). Multiplying by 1.0 forces floating-point division on engines where integer division would truncate. Three columns in the output: numerator, denominator, ratio. The dashboard renders all three so the consumer can sanity-check the rate against the absolute counts.

•Compute the numerator and denominator separately

Run one query for total signups
Run another query for converters
Compute the ratio in application code
Two queries, two round trips, two places where the join logic can drift

✓Compute both with conditional aggregation

One query, one round trip, both numbers in the same row
Ratio is computed in SQL alongside the absolute values
Numerator and denominator are visible to the consumer for sanity-checking
If the join logic changes, both numbers change together; no drift

Sum-of-1 vs Count: equivalent but different intent

Some teams write SUM(CASE WHEN condition THEN 1 ELSE 0 END) instead of COUNT(CASE WHEN condition THEN 1 END). Both produce the same result for counting matching rows. SUM is more explicit; COUNT is more idiomatic. Pick whichever your codebase uses; both are correct. The interviewer reads either form as competent; mixing the two in the same query reads as inconsistent style.

The 1 in COUNT(CASE WHEN ... THEN 1 END) is not magic; it is a non-NULL sentinel. You could write COUNT(CASE WHEN ... THEN order_id END) and it would count the same rows (assuming order_id is never NULL for the matching rows). The 1 is the convention because it is short and unambiguous: 'count this row if the condition is true.'

One Pass Replaces Many WHERE Queries

Daily Life

Interviews

Handle conditional aggregation with multiple dimensions and nested CASE logic without losing readability.

Modern SQL has a cleaner syntax for conditional aggregation: the FILTER clause. SUM(amount) FILTER (WHERE tier = 'premium') is equivalent to SUM(CASE WHEN tier = 'premium' THEN amount END), but it reads as English and separates the aggregate from the filter visually. The FILTER clause is in the SQL standard since 2003 and supported on Postgres, SQLite, and DuckDB. It is not supported on MySQL, SQL Server, BigQuery, Snowflake, or Redshift. State the engine when you reach for it: 'on Postgres I would use FILTER; on Snowflake I'd fall back to CASE.'

supported on Postgres, SQLite, DuckDB. Reads as English: SUM(amount) FILTER (WHERE tier = 'premium').
supported on every engine. Portable for cross-engine code; the lowest common denominator.
FILTER for codebases targeting one engine; CASE for portable code or when the engine doesn't support FILTER.

The FILTER clause syntax

	/* Same query, FILTER clause version (Postgres / SQLite / DuckDB) */
	SELECT
	region,
	SUM(amount) FILTER (
	WHERE tier = 'premium'
	) AS premium_revenue,
	SUM(amount) FILTER (
	WHERE tier = 'standard'
	) AS standard_revenue,
	SUM(amount) AS total_revenue,
	COUNT(*) AS order_count,
	COUNT(*) FILTER (
	WHERE status = 'completed'
	) AS completed_count
	FROM orders
	GROUP BY region

Why the FILTER form is preferred where supported

Three reasons. First: readability. SUM(amount) FILTER (WHERE tier = 'premium') reads as 'sum the amount column, filtered to premium-tier rows.' SUM(CASE WHEN tier = 'premium' THEN amount END) reads as 'sum the case expression that returns amount or null.' The FILTER form is one mental step; the CASE form is two. Second: separation of concerns. The aggregate (SUM) and the filter (tier = 'premium') are visually distinct, so the reader can parse them independently. Third: no ELSE confusion. The FILTER form does not have the 'do I write ELSE 0 or ELSE NULL' decision; the filter excludes non-matching rows entirely.

When the FILTER form is wrong to reach for

If the SQL has to run on multiple engines (a query that ships to both a Postgres OLTP and a Snowflake warehouse), the FILTER form is not portable. The CASE form is portable. For team codebases that target one engine and that engine supports FILTER, FILTER is the better default; for cross-engine code, CASE is the safe default. Mention this in the interview when you write FILTER: 'on this engine FILTER is supported; if the query needed to run on multiple engines, I would fall back to CASE for portability.'

•Reach for FILTER when

The engine supports it (Postgres, SQLite, DuckDB)
Readability is the primary concern
The codebase already uses FILTER consistently
The team wants to avoid the ELSE 0 vs ELSE NULL question

✓Stick with CASE when

The engine does not support FILTER (Snowflake, BigQuery, SQL Server, MySQL, Redshift)
The query has to be portable across engines
The team's house style is CASE
The condition is complex and would be hard to read in a WHERE clause

Other engine-specific shortcuts

Two other engine features worth knowing. Postgres also supports SUM(...) FILTER (WHERE ...) and AVG(...) FILTER (WHERE ...), not just on SUM and COUNT. Snowflake has BOOLOR_AGG, BOOLAND_AGG, and IFF, which combined with CASE produce similar effects. BigQuery has COUNTIF and SUMIF (the latter in standard SQL via SUM(IF(...))) for shorter forms. These are engine-specific; the CASE form is the lowest common denominator that ships everywhere.

The composability bonus

The conditional aggregation pattern composes with everything else in SQL. You can put it inside a CTE, combine it with window functions, nest it inside another aggregate. SUM(CASE WHEN ... THEN 1 END) OVER (PARTITION BY region) computes a running count of matching rows per region. This is useful for rolling metrics like 'cumulative active users per region over time' or 'count of orders this month with status active.' The base pattern is the same; the OVER clause makes it windowed instead of grouped.

TIP

When you write conditional aggregates, line up the column aliases in the SELECT list. Aligning premium_revenue, standard_revenue, total_revenue makes the three numbers visually parallel in the output. The interviewer reads alignment as a code-review habit; it costs nothing and signals attention to readability.

Reading a CASE-Inside-SUM Out Loud

Daily Life

Interviews

Explain to the interviewer why conditional aggregation outperforms multiple filtered subqueries joined together.

Past correctness, the interviewer probes whether you understand the difference between filtering with WHERE and filtering inside an aggregate. The two produce different shapes; mixing them up is a common bug. This section covers the WHERE vs CASE distinction, the NULL-in-aggregate gotcha, and the divide-by-zero defense for ratios.

WHERE filters the rows; CASE filters the contribution

The two operations look similar but produce different results. WHERE tier = 'premium' removes non-premium rows from the source before aggregation; the resulting groups have only premium rows. SUM(CASE WHEN tier = 'premium' THEN amount END) keeps all rows in the source but only sums premium amounts; the resulting groups include non-premium rows in COUNT(*) and in other unconditional aggregates. The difference matters when you need both filtered and unfiltered metrics in the same row.

	SELECT
	region,
	SUM(amount) AS premium_revenue,
	SUM(amount) AS total_revenue
	FROM orders
	WHERE tier = 'premium'
	GROUP BY region ;


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

The CASE form is the right pattern when the output needs metrics across multiple slices. The WHERE form is the right pattern when the output only needs metrics from one slice (and you want the engine to skip the non-matching rows entirely). Mixing them up: the WHERE filters at the source level, and any subsequent unconditional aggregates see only the filtered rows. State this when the interviewer asks 'why CASE here and not WHERE?': 'CASE because the output needs both the premium slice and the total; WHERE would filter out the non-premium rows and total_revenue would equal premium_revenue.'

The NULL-in-aggregate gotcha

Aggregate functions skip NULL. SUM, COUNT, AVG, MAX, MIN all ignore rows where the column being aggregated is NULL. This is what makes CASE WHEN ... THEN value END work without ELSE: non-matching rows return NULL, which the aggregate skips. The gotcha: COUNT(*) does not skip NULL because it does not look at a column; it counts every row in the group. COUNT(column) skips rows where the column is NULL. The two are different functions; mixing them up changes the row count.

SUM, COUNT(col), AVG, MAX, MIN all skip NULL rowsCOUNT(*) does not look at a column; counts every rowCASE WHEN ... THEN value END returns NULL for non-matchesAggregate sees NULL, skips the row, no contributionThis is what makes conditional aggregation work

	/* Three different counts; each one means something different */
	SELECT
	region,
	COUNT() AS total_rows, / all rows */
	COUNT(amount) AS non_null_amounts, /* rows where amount IS NOT NULL */
	COUNT(DISTINCT customer_id) AS unique_customers, /* distinct customer_ids */
	COUNT(
	CASE
	WHEN status = 'active' THEN 1
	END
	) AS active_rows /* conditional */
	FROM orders
	GROUP BY region

Divide-by-zero defense for ratios

Conditional aggregates often produce ratios: conversion rate, completion rate, premium share. The denominator can be zero (a region with no signups produces COUNT(*) = 0). Division by zero throws an error on most engines. The defense is NULLIF on the denominator: NULLIF(COUNT(*), 0) returns NULL when the count is 0, and NULL divided by anything is NULL, which is the right answer for 'undefined ratio.' Always wrap the divisor in NULLIF for ratio columns.

	/* Ratio with divide-by-zero defense */
	SELECT
	region,
	COUNT(*) AS signups,
	COUNT(
	CASE
	WHEN converted = TRUE THEN 1
	END
	) AS converters,
	COUNT(
	CASE
	WHEN converted = TRUE THEN 1
	END
	) * 1 / NULLIF(COUNT(*), 0) AS conversion_rate
	FROM events
	GROUP BY region

Boolean shortcuts on engines that support them

Some engines treat boolean columns as 0/1 numerics when summed. SUM(is_active::int) on Postgres counts the TRUE rows. SUM(CAST(is_active AS INT)) on Snowflake does the same. This is a shortcut; it reads slightly worse than the CASE form but is shorter. The CASE form is portable and idiomatic; pick it for interview code unless the engine-specific shortcut is the codebase's house style.

At Wayfair in 2022, a marketing analytics team rebuilt the daily KPI dashboard query after the original shipped a NULL-handling bug. The query used SUM(CASE WHEN segment = 'premium' THEN amount END) to compute premium revenue, but the segment column had NULL values for new customers who had not yet been classified. Those rows were silently excluded from every slice (premium, standard, basic), but COUNT(*) included them in the 'total customers' metric. The dashboard showed total revenue across slices as less than total_revenue (the unconditional sum), and the discrepancy was the unclassified bucket. The fix was to add an 'unclassified' slice as another conditional column (SUM(CASE WHEN segment IS NULL THEN amount END)), so all rows were accounted for. The runbook line was 'every conditional aggregate dashboard query in this codebase has an unclassified slice; if the sum of slices does not equal the unconditional aggregate, the query has a bug.' Candidates who name the unclassified-slice discipline unprompted read as someone who has built KPI dashboards.

Situation	Phrasing that flatlines	Phrasing that lands
You see 'one row per region with metrics by segment'	"I'll write three queries and UNION them."	"This is conditional aggregation. One GROUP BY with SUM(CASE WHEN segment = 'X' THEN amount END) for each slice. One query, one row per group."
The interviewer asks 'why CASE instead of WHERE'	"Same thing."	"WHERE filters the source rows before aggregation; CASE filters the contribution inside the aggregate. If the output needs both the slice metric and the total, WHERE would remove the non-slice rows and the total would equal the slice."
The data has NULLs in the slice column	"I'll ignore them."	"NULL rows are excluded from every slice's conditional aggregate but included in COUNT(*). The sums won't add up to the total; I'd add an 'unclassified' slice for NULL rows so the math reconciles."
The output is a ratio	"I'll divide."	"NULLIF on the denominator. A group with zero matching rows produces a zero denominator; NULLIF converts that to NULL so the ratio is NULL instead of a divide-by-zero error."
The engine is Postgres	"I'll use CASE."	"FILTER clause: SUM(amount) FILTER (WHERE tier = 'premium'). Reads cleaner than CASE; supported on Postgres, SQLite, DuckDB. Falls back to CASE on Snowflake, BigQuery, MySQL."

The closing summary

Close with a four-sentence wrap. 'Conditional aggregation produces multiple slice metrics in one row of output by wrapping CASE WHEN expressions inside SUM, COUNT, AVG. The pattern is SUM(CASE WHEN condition THEN value END) for summed metrics and COUNT(CASE WHEN condition THEN 1 END) for filtered counts. WHERE filters the source rows; CASE filters the contribution inside the aggregate, so use CASE when the output needs both filtered and unfiltered metrics. For ratios, wrap the denominator in NULLIF to defend against divide-by-zero; add an unclassified slice when the slice column can be NULL so the sums reconcile to the total.' Four sentences. Pattern, syntax variants, WHERE vs CASE, defense. The shape generalizes to every KPI dashboard query.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering phone screen at an e-commerce company. The interviewer asks: 'Show me one row per region with active customer count, churned customer count, premium revenue, standard revenue, and total revenue, all from the customers and orders tables.'

You say: 'This is conditional aggregation. One GROUP BY region, with multiple CASE-wrapped aggregates for each slice.'

You write the SELECT with five conditional columns: COUNT(CASE WHEN status='active' THEN 1 END) for the active count, COUNT(CASE WHEN status='churned' THEN 1 END) for churned, SUM(CASE WHEN tier='premium' THEN amount END) for premium revenue, SUM(CASE WHEN tier='standard' THEN amount END) for standard, SUM(amount) for total.

You walk through a small example: two regions, three rows each. The conditional counts and sums slice the metrics; the unconditional aggregates give the totals.

Follow-up: 'What if status is NULL for some new customers?' You say: 'NULL rows are excluded from every conditional count but included in COUNT(*). The slices won't sum to the total; I'd add an unclassified slice (COUNT(CASE WHEN status IS NULL THEN 1 END)) so the math reconciles.'

Follow-up: 'What about conversion rate?' You say: 'Both numerator and denominator computed in the same row: COUNT(CASE WHEN converted THEN 1 END) * 1.0 / NULLIF(COUNT(*), 0). NULLIF defends against divide-by-zero for empty groups.'

Closing: 'On Postgres I would use the FILTER clause for readability: SUM(amount) FILTER (WHERE tier = 'premium'). The CASE form is portable across every engine; FILTER is cleaner where supported.'

KEY TAKEAWAYS

When the requested output puts counts or sums for several slices in the same row, that is conditional aggregation: one GROUP BY pass with one aggregate column per slice, not one query per slice.

WHERE removes rows from the group; CASE inside the aggregate removes only that row's contribution. That is why SUM(amount) under a WHERE filter equals the filtered slice instead of the true total.

Use SUM(CASE WHEN cond THEN amount ELSE 0 END) for sums and COUNT(CASE WHEN cond THEN 1 END) with no ELSE for counts, since COUNT counts a 0 but skips a NULL.

Filtered distinct counts come from COUNT(DISTINCT CASE WHEN status = 'active' THEN user_id END), because non-matching rows evaluate to NULL and drop out of the distinct set.

Wrap every ratio denominator in NULLIF(COUNT(*), 0) and multiply the numerator by 1.0: that avoids a divide-by-zero error on empty groups and integer truncation on engines that floor integer division.

The FILTER clause, as in SUM(amount) FILTER (WHERE tier = 'premium'), is cleaner but only runs on Postgres, SQLite, and DuckDB. CASE is the portable form for cross-engine code.

CASE inside SUM replaces ten separate queries with one

Category: SQL
Difficulty: beginner
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: When the Output Is a Pivot-Like Summary, SUM(CASE WHEN ... THEN 1 ELSE 0 END), Counting Subsets Without Extra Queries, One Pass Replaces Many WHERE Queries, Reading a CASE-Inside-SUM Out Loud

Lesson Sections

When the Output Is a Pivot-Like Summary (concepts: sqlConditionalAgg)
What conditional aggregation actually does A normal aggregate (SUM, COUNT, AVG) operates on every row in the group. A conditional aggregate operates on every row in the group but only counts the ones matching the CASE condition. SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) counts active rows. SUM(CASE WHEN status='active' THEN amount ELSE 0 END) sums amounts from active rows. The CASE inside the aggregate filters what contributes to the sum, while the row itself stays in the group. This is w
SUM(CASE WHEN ... THEN 1 ELSE 0 END) (concepts: sqlConditionalAgg)
The fundamental form is SUM(CASE WHEN condition THEN value ELSE 0 END). The CASE returns the value when the condition is TRUE, and 0 otherwise. SUM adds them all up. The result is the sum of values from matching rows; non-matching rows contribute 0 and do not affect the total. The pattern works because SUM ignores NULL and treats 0 as additive identity; both routes (NULL or 0) give the right answer. The canonical query Reading the pattern GROUP BY region collapses the orders to one row per regio
Counting Subsets Without Extra Queries (concepts: sqlConditionalAgg)
COUNT has a quirk that makes the conditional pattern slightly different from SUM. COUNT(*) counts every row in the group. COUNT(column) counts rows where column is not NULL. COUNT(CASE WHEN condition THEN 1 END) counts rows where the condition is TRUE (because non-matching rows return NULL, which COUNT skips). The 1 in the THEN is a sentinel; any non-NULL value works (COUNT does not care about the value, only that it is non-NULL). The canonical COUNT pattern Why no ELSE in the COUNT version In C
One Pass Replaces Many WHERE Queries (concepts: sqlConditionalAgg)
Modern SQL has a cleaner syntax for conditional aggregation: the FILTER clause. SUM(amount) FILTER (WHERE tier = 'premium') is equivalent to SUM(CASE WHEN tier = 'premium' THEN amount END), but it reads as English and separates the aggregate from the filter visually. The FILTER clause is in the SQL standard since 2003 and supported on Postgres, SQLite, and DuckDB. It is not supported on MySQL, SQL Server, BigQuery, Snowflake, or Redshift. State the engine when you reach for it: 'on Postgres I wo
Reading a CASE-Inside-SUM Out Loud (concepts: sqlConditionalAgg)
Past correctness, the interviewer probes whether you understand the difference between filtering with WHERE and filtering inside an aggregate. The two produce different shapes; mixing them up is a common bug. This section covers the WHERE vs CASE distinction, the NULL-in-aggregate gotcha, and the divide-by-zero defense for ratios. WHERE filters the rows; CASE filters the contribution The CASE form is the right pattern when the output needs metrics across multiple slices. The WHERE form is the ri