Thinking habit: Know what one row means
Pivot rows to columns in SQL with CASE WHEN
The question: Show one row per region with its Q1 and Q2 sales in two columns.
sales
| region | quarter | amount |
|---|---|---|
| North | Q1 | 100 |
| North | Q2 | 150 |
| North | Q2 | 50 |
| South | Q1 | 80 |
| South | Q2 | 120 |
This query looks right. Run it, decide if it is, and fix it.
SELECT region, CASE WHEN quarter = 'Q1' THEN amount END AS q1, CASE WHEN quarter = 'Q2' THEN amount END AS q2 FROM sales;
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT region, CASE WHEN quarter = 'Q1' THEN amount END AS q1, CASE WHEN quarter = 'Q2' THEN amount END AS q2 FROM sales;
CASE works row by row, so you still get one row per sale, mostly NULLs. Nothing adds the rows of a region together.
The answer
SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2 FROM sales GROUP BY region;
CASE picks which column each amount belongs in; SUM with GROUP BY region adds them into one row per region. North's two Q2 sales become 200.
ELSE 0 shows 0 for a quarter with no sales instead of NULL. The same pattern with COUNT gives conditional counts.
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.