Pivoting Revenue by Quarter

SQLPivotSeniorStaffUpdated Nov 10, 2026

In PostgreSQL, pivot 2025 orders into one row per region with columns q1, q2, q3, q4 holding the sum of total for that quarter.

Schema

CREATE TABLE orders (
  id        BIGINT PRIMARY KEY,
  region    TEXT NOT NULL,
  total     NUMERIC NOT NULL,
  placed_at DATE NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (a)

SELECT region,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 1 THEN total ELSE 0 END) AS q1,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 2 THEN total ELSE 0 END) AS q2,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 3 THEN total ELSE 0 END) AS q3,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 4 THEN total ELSE 0 END) AS q4
FROM orders
WHERE EXTRACT(YEAR FROM placed_at) = 2025
GROUP BY region;

Conditional aggregation (SUM(CASE WHEN ... )) is the portable Postgres pivot pattern: one row per region, one column per quarter. (b) puts CASE outside SUM, so SUM still collapses by group and the CASE just nullifies most rows; the extra GROUP BY placed_at also breaks the row-per-region requirement. (c) leaves the result long, not pivoted. (d) uses Oracle/T-SQL PIVOT syntax that Postgres does not support.

The Options That Fail

The explanation above says why each of these breaks.

Option (b)

SELECT region,
       CASE WHEN EXTRACT(QUARTER FROM placed_at) = 1 THEN SUM(total) END AS q1,
       CASE WHEN EXTRACT(QUARTER FROM placed_at) = 2 THEN SUM(total) END AS q2,
       CASE WHEN EXTRACT(QUARTER FROM placed_at) = 3 THEN SUM(total) END AS q3,
       CASE WHEN EXTRACT(QUARTER FROM placed_at) = 4 THEN SUM(total) END AS q4
FROM orders
WHERE EXTRACT(YEAR FROM placed_at) = 2025
GROUP BY region, placed_at;

Option (c)

SELECT region, quarter, SUM(total) AS q
FROM orders
WHERE EXTRACT(YEAR FROM placed_at) = 2025
GROUP BY region, EXTRACT(QUARTER FROM placed_at);

Option (d)

SELECT *
FROM orders
PIVOT (SUM(total) FOR EXTRACT(QUARTER FROM placed_at) IN (1, 2, 3, 4))
WHERE EXTRACT(YEAR FROM placed_at) = 2025;