Pivot with SUM(CASE)

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• 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.

Items to match
• 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(*)
Definitions
• Quantity sold in the West
• Number of sales in the West
• Average price of the sales in the West
• Number of sales, all regions together

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.

Starter code
-- Write your query below
Back to the course