Pivot with SUM(CASE)
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Counting with a condition
Put a CASE inside an aggregate and you can count or add only the rows that match a condition. Each matching row contributes 1, the others 0:
SELECT SUM(CASE WHEN power >= 75 THEN 1 ELSE 0 END) AS strong_count,
COUNT(*) AS total
FROM codemon;Add GROUP BY and you get the count per group — here, per type, the number of Codémons that win more than they lose:
SELECT type,
COUNT(*) AS total,
SUM(CASE WHEN wins > losses THEN 1 ELSE 0 END) AS winners
FROM codemon
GROUP BY type
ORDER BY type;COUNT works too
COUNT(CASE WHEN wins > losses THEN 1 END) gives the same result: without ELSE, the other rows become NULL, and COUNT ignores NULL.
Exercise 2 — Rows into columns: the pivot
The shop manager wants one line per category with the quantity sold in each region side by side. A plain GROUP BY category, region gives one row per combination instead:
SELECT category, region, SUM(quantity) AS qty
FROM sales
GROUP BY category, region
ORDER BY category, region;To turn the regions into columns, write one SUM(CASE ...) per region. This is called a pivot:
SELECT category,
SUM(CASE WHEN region = 'North' THEN quantity ELSE 0 END) AS north,
SUM(CASE WHEN region = 'South' THEN quantity ELSE 0 END) AS south,
SUM(CASE WHEN region = 'East' THEN quantity ELSE 0 END) AS east,
SUM(CASE WHEN region = 'West' THEN quantity ELSE 0 END) AS west
FROM sales
GROUP BY category;You must know the columns in advance
Each column is written by hand, so a pivot only works when the list of values (here the four regions) is known and short.
Exercise 3 — Pick the counter
Which expression returns the number of sales in the Medicine category?
SUM(CASE WHEN category = 'Medicine' THEN 1 ELSE 0 END)SUM(CASE WHEN category = 'Medicine' THEN quantity ELSE 0 END)COUNT(CASE WHEN category = 'Medicine' THEN 1 ELSE 0 END)COUNT(category = 'Medicine')Hint : COUNT counts every non-NULL value, and 0 is not NULL. Quantities are not a number of sales either.
Exercise 4 — Finish the pivot
Complete the pivot that counts the number of sales per category in the North and in the South.
Hint : Add up the 1s and 0s, give the 0 to the non-matching rows, and remember what makes one row per category.
Exercise 5 — Conditional averages and percentages
Without ELSE, the non-matching rows become NULL — and aggregates ignore NULL. That is perfect for a conditional average: the average power of the fire Codémons only, next to the overall average:
SELECT ROUND(AVG(power), 1) AS avg_all,
ROUND(AVG(CASE WHEN type = 'fire' THEN power END), 1) AS avg_fire
FROM codemon;Combine a conditional count with the total and you get a percentage — here the share of Codémons that have a nickname:
SELECT ROUND(100.0 * SUM(CASE WHEN nickname IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS nickname_pct
FROM codemon;ELSE 0 or no ELSE?
For SUM, ELSE 0 and no ELSE give the same total (except when no row matches: NULL instead of 0). For AVG and COUNT they do not: a 0 is counted, a NULL is ignored. Choose on purpose.
Exercise 6 — Read the pivot
Match each expression of a sales report with what it computes.
SUM(CASE WHEN region = 'West' THEN quantity ELSE 0 END)SUM(CASE WHEN region = 'West' THEN 1 ELSE 0 END)AVG(CASE WHEN region = 'West' THEN price END)COUNT(*)Exercise 7 — Type scoreboard
Professor Pi wants a scoreboard with one line per type.
Write a query that returns, for each type:
1. the type
2. the total number of wins, named total_wins
3. the number of Codémons with more wins than losses, named winners
4. the number of Codémons with a power of 75 or more, named strong
Sort by total_wins descending, then by type.
-- Write your query below