Pivot Quarterly Sales Into Columns — SQL

Solving tips

Recognize a pivot: distinct values of one column need to become separate output columns, so reach for one aggregate per target column.
Use `SUM(...) FILTER (WHERE ...)` per quarter and wrap it in `COALESCE(..., 0)` so products missing a quarter show 0 instead of NULL.
GROUP BY only the row key (product); the quarter is consumed by the FILTER clauses, not the GROUP BY.

Sales are stored one row per product per quarter. Reshape them so each product is a single row with one column per quarter.

Schema

CREATE TABLE sales (
  product  TEXT,
  quarter  TEXT,   -- 'Q1' | 'Q2' | 'Q3' | 'Q4'
  amount   INTEGER
);

Sample data:

product	quarter	amount
Widget	Q1	100
Widget	Q2	150
Widget	Q3	200
Widget	Q4	50
Gadget	Q1	80
Gadget	Q2	90
Gadget	Q4	120

Task

Return one row per product with columns product, q1, q2, q3, q4, where each quarter column is the total amount for that product in that quarter. A product with no rows for a quarter must show 0 in that column. Order by product ascending.

Expected output

product	q1	q2	q3	q4
Gadget	80	90	0	120
Widget	100	150	200	50

Approach

This is a fixed-set pivot: the target columns (Q1–Q4) are known ahead of time, so we group by the row key and compute one conditional aggregate per column. PostgreSQL’s aggregate FILTER clause is the cleanest way to express “sum only the rows for this quarter,” and COALESCE turns the NULL an empty group would produce into 0.

Query

SELECT
  product,
  COALESCE(SUM(amount) FILTER (WHERE quarter = 'Q1'), 0) AS q1,
  COALESCE(SUM(amount) FILTER (WHERE quarter = 'Q2'), 0) AS q2,
  COALESCE(SUM(amount) FILTER (WHERE quarter = 'Q3'), 0) AS q3,
  COALESCE(SUM(amount) FILTER (WHERE quarter = 'Q4'), 0) AS q4
FROM sales
GROUP BY product
ORDER BY product;

Walkthrough

Rows collapse into one group per product. Within the Gadget group there are three rows (Q1=80, Q2=90, Q4=120); each FILTER picks out just the matching quarter, so q1=80, q2=90, q4=120, and the Q3 filter matches nothing, giving NULL which COALESCE rewrites to 0. The Widget group has all four quarters, producing 100, 150, 200, 50. ORDER BY product puts Gadget before Widget, matching the expected output.

Complexity & notes

Single grouped scan of the table, O(n). Watch for these pitfalls: without COALESCE the missing Q3 for Gadget returns NULL, not 0; do not add quarter to the GROUP BY or you get one row per product-quarter again. Dialect differences: FILTER is standard SQL and supported in PostgreSQL, but MySQL lacks it, where you would use SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) instead. PostgreSQL also offers the crosstab function via the tablefunc extension, but for a small fixed column set the conditional-aggregate form is clearer and needs no extension.