SQL Interview Prep · Lesson

Pivoting With Conditional Aggregation

The portable CASE-inside-SUM pattern for turning rows into columns.

Pivoting With Conditional Aggregation is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Interview Setup

One of the most common reporting interview tasks is: turn rows into columns. You have a long table like sales(region, quarter, amount) and the interviewer wants a wide report with one column per quarter.

The portable, dialect-independent answer they want to hear is conditional aggregation: a CASE expression placed inside an aggregate such as SUM. Master this and you can pivot in any database, even ones with no PIVOT keyword.

Long vs Wide Form

Before pivoting, name the shapes. Long form stores one fact per row: each region/quarter pair is its own row. Wide form spreads a category across columns.

Long: easy to insert, hard to read side by side.
Wide: great for a human-facing report.

A pivot transforms long into wide. Interviewers love this because it tests whether you understand aggregation, not just syntax.

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

The Core Pattern

The trick: for each output column, write a CASE that returns the value when the row matches that column, and NULL otherwise. Wrap it in an aggregate so the group collapses to one row per key.

Read it as: sum the amount, but only for the Q1 rows. Because SUM ignores NULL, the non-matching rows contribute nothing.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

Why SUM Ignores NULL

This pattern works because of one fact interviewers will probe: aggregate functions skip NULLs. A CASE with no ELSE returns NULL when no branch matches, so SUM(CASE WHEN ... THEN amount END) only adds the rows you selected.

If you wrote ELSE 0 instead, it would also work for SUM (adding zero changes nothing) but would break AVG, MIN, and COUNT.

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

Worked Example: Quarterly Report

Here is the full query against the sample data. Each region becomes one row; each quarter becomes one column.

The GROUP BY region is what collapses the four input rows into two output rows. Without it, you would get one row per input row with mostly NULLs.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

Choosing the Right Aggregate

The aggregate you wrap the CASE in must match the question:

SUM when each cell totals values.
MAX or MIN when each region/quarter pair has exactly one value and you just want to surface it.
COUNT when each cell counts matching rows.

Interviewers often ask the COUNT variant: how many orders per status per month?

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

MAX for One-Value Cells

When each key/category pair holds a single value (a true cross-tab, not a total), use MAX or MIN. Both return the lone non-NULL value and ignore the NULLs from non-matching branches.

This is the safe choice when you are reshaping attributes rather than summing money, for example turning a key/value settings table into one row per entity.

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

Handling NULL Output Cells

If a region had no Q2 sales, its q2 cell comes out NULL. Interviewers may ask you to show 0 instead. Wrap the whole aggregate in COALESCE.

Put COALESCE on the outside of the aggregate, not inside the CASE, so you only substitute when the entire group has no matching rows.

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

Adding a Grand Total Column

A common follow-up: add a total across all the pivoted columns. You do not need to add the columns by name. A plain SUM(amount) over the same group gives the row total because it ignores the CASE filtering entirely.

This shows the interviewer you understand that each aggregate in the SELECT is computed independently over the same group.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

The Filtered Aggregate Shortcut

PostgreSQL and the SQL standard support FILTER (WHERE ...), a cleaner way to write conditional aggregation. It reads better and avoids the CASE boilerplate.

Mention this in an interview to show breadth, but know that MySQL and SQL Server do not support it, so CASE remains the portable answer.

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

The Big Limitation

Conditional aggregation has one catch interviewers will press on: you must list every output column by hand. If quarters or categories are not known in advance, this static query cannot adapt.

That problem is called a dynamic pivot, and it needs generated SQL. For a fixed, known set of categories, though, conditional aggregation is the clean, portable winner.

Quick Check

Test your grasp of the conditional aggregation pattern.

Recap

Conditional aggregation is the portable pivot every interviewer accepts:

One CASE per output column, wrapped in an aggregate.
SUM for totals, MAX/MIN for single-value cells, COUNT for counts.
Works because aggregates ignore the NULL from non-matching branches.
Use COALESCE to turn empty cells into 0.
Limitation: columns must be hard-coded, which leads to dynamic pivots next.

Frequently asked questions

Is the “Pivoting With Conditional Aggregation” lesson free?

Yes — the full text of “Pivoting With Conditional Aggregation” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Pivoting With Conditional Aggregation”?

The portable CASE-inside-SUM pattern for turning rows into columns. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Pivoting With Conditional Aggregation” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep