Pivot with CASE in SQL

Level 5288 keystrokes

CASE inside aggregate functions creates pivot table-like output.

SQL code

-- One row per product, one column per month
1SELECT
2 product_name,
-- Conditional sum: add quantity only for January sales
3 SUM(
-- MONTH() gives 1-12; other months add 0
4 CASE WHEN MONTH(sale_date) = 1
5 THEN quantity ELSE 0 END
6 ) AS jan,
-- Same pattern for February
7 SUM(
8 CASE WHEN MONTH(sale_date) = 2
9 THEN quantity ELSE 0 END
10 ) AS feb,
-- ...and for March
11 SUM(
12 CASE WHEN MONTH(sale_date) = 3
13 THEN quantity ELSE 0 END
14 ) AS mar
-- No year filter, so the same month of different years is added together
15FROM
16 sales
-- One group, and one output row, per product
17GROUP BY
18 product_name
-- Sort alphabetically by product name
19ORDER BY
20 product_name;

Output

+--------------+-----+-----+-----+
| product_name | jan | feb | mar |
+--------------+-----+-----+-----+
| Laptop       |  10 |  15 |  12 |
| Phone        |  25 |  30 |  28 |
| Tablet       |   8 |  10 |  15 |
+--------------+-----+-----+-----+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 6
  • (× 6
  • )× 6
  • ,× 3
  • =× 3
  • ;× 1

More SQL examples at level 5

← SQL typing practice