Pivot: sales per product for each month

Medium

Question

The product_sales table has one row per sale. Turn it into a table with one row per product and one column for each of the first three months of 2026.

Return product, jan, feb and mar, where each month column is the total amount sold in that month of the year 2026. A month with no sale shows 0, not NULL.

Only look at sales from January, February and March 2026. A product with no sale in those three months must not appear. The order of the rows does not matter.

pivot
conditional aggregation
case
strftime
null

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

product_sales4 columns · 12 rowsnot run yet

CREATE TABLE product_sales (id INTEGER PRIMARY KEY, product TEXT NOT NULL, sold_on TEXT NOT NULL, amount INTEGER NOT NULL);

Example rows of the product_sales table
id	product	sold_on	amount
1	Tea	2026-01-05	100
2	Tea	2026-01-20	150
3	Tea	2026-03-02	80
4	Coffee	2026-02-10	200
5	Coffee	2026-02-25	50
6	Coffee	2026-03-15	120
7	Biscuits	2026-01-31	60
8	Biscuits	2026-02-01	70
9	Biscuits	2026-03-31	90
10	Juice	2026-04-10	300
11	Tea	2025-02-14	999
12	Coffee	2026-04-02	40

About this SQL

SQLite has no PIVOT keyword (SQL Server and Oracle do), so a pivot is conditional aggregation: SUM(CASE WHEN ... THEN amount ELSE 0 END), once for each output column. strftime('%m', x) returns TEXT such as '02', so compare it with '02', not with 2.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready