SQL Roadmap/Aggregation & Grouping

Advanced Aggregation & Pivoting

Wrapping CASE inside SUM/COUNT computes several conditional totals in one pass over the data — the portable, engine-agnostic way to turn rows into columns (a "pivot") without a database-specific PIVOT operator.

!!3/5Theory: 25m3 problems

Conditional aggregation: CASE inside an aggregate function

The core trick of this entire subtopic is one composition: put a CASE expression inside an aggregate function's argument, so the aggregate only "sees" a value for rows matching the condition, and sees NULL (which every aggregate function silently ignores) for everything else.

SELECT department, COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count, COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female_count, SUM(CASE WHEN status = 'shipped' THEN amount END) AS shipped_revenue FROM orders GROUP BY department;

Read CASE WHEN gender = 'Male' THEN 1 END as "1 if this row is male, otherwise NULL" (the implicit ELSE NULL when you omit an explicit ELSE, covered in String & Date Functions, is exactly what makes this pattern work). COUNT then counts only the non-NULL results — i.e., only the male rows — while still processing every row in a single pass over the table, computing every conditional total in parallel rather than requiring a separate query (or self-join) per condition.

This is meaningfully different from filtering with WHERE first: WHERE gender = 'Male' before grouping would only let you compute the male count, discarding every female row before it could contribute to a different column of the same result. Conditional aggregation is how you get multiple, differently-filtered totals side by side in one row per group.

Pivoting: turning rows into columns

"Pivot" is exactly conditional aggregation applied to reshape data — take a category that currently spans multiple rows and spread it across columns instead:

-- Input: one row per (department, gender) with a headcount -- Output: one row per department, with separate male/female columns SELECT department, SUM(CASE WHEN gender = 'male' THEN headcount ELSE 0 END) AS male, SUM(CASE WHEN gender = 'female' THEN headcount ELSE 0 END) AS female FROM department_gender_counts GROUP BY department;

Some engines (SQL Server, Oracle, Snowflake, BigQuery) offer a native PIVOT operator, but the CASE-inside-SUM/COUNT/MAX pattern above works identically on every SQL engine, including MySQL and PostgreSQL, which lack a native PIVOT keyword — this portability, plus the fact that it composes with every other SQL feature you already know (it's just an expression), is why the manual pattern is worth knowing cold even on engines that do have a native operator.

One subtlety: use SUM/COUNT with an explicit numeric fallback (ELSE 0, or rely on NULL-skipping for SUM/COUNT) for numeric pivots, but use MAX/MIN (not SUM) when pivoting non-numeric or single-valued-per-group columns — summing text makes no sense, and MAX(CASE WHEN category = 'x' THEN name END) is the idiomatic way to pull a single matching value out per group.

Why interviewers care

This is one of the highest-leverage "do they actually understand aggregation, or just recognize GROUP BY" tests available, because the pattern requires correctly reasoning about when each part of the query executes: the CASE runs per-row, before the aggregate collapses the group — get that order backwards and the query either errors or silently produces nonsense. It's also a direct, practical FAANG-analytics-style question: "give me a report with one row per month and one column per product category" is a real, common request, and conditional aggregation is the actual production answer to it.

Pitfalls and interview gotchas

  • Filtering with WHERE when the intent was a conditional aggregate, which discards rows needed for the other columns of the same pivoted result.
  • Using SUM to pivot a non-numeric column instead of MAX/MIN.
  • Forgetting the implicit ELSE NULL and being surprised that COUNT(CASE WHEN ... THEN 1 END) doesn't need an explicit ELSE 0 — it works precisely because COUNT skips NULL, whereas SUM(CASE WHEN ... THEN 1 END) also technically works the same way but is easy to mistakenly "fix" with an unnecessary ELSE 0 that doesn't change the result but exposes shaky understanding of why it works.
  • Trying to pivot into a genuinely dynamic (unknown-at-query-time) set of columns. The CASE pattern requires knowing the column labels when you write the query — dynamic pivoting (an unknown number of categories) requires generating the SQL programmatically, which is a fundamentally different, non-portable problem outside this roadmap's scope.

Where this goes next

Subqueries & CTEs (next) moves from "summarize a group" to "answer a question about the group's own aggregate result" — comparing each row to its group's average, or filtering to only the group with the single highest value, both of which need a subquery (or, as an alternative covered later, a window function) rather than a plain GROUP BY.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.