Pivoting With Conditional Aggregation

One aggregate per output column

SQL results have a fixed set of columns, so a pivot lists each output column explicitly as a conditional aggregate: sum(amount) FILTER (WHERE month = ...). Use half-open date ranges (>= start and < next start) so boundaries are not double counted. Starting from a LEFT JOIN on the dimension (customers) keeps rows with no data. For dynamic columns, generate SQL in application code or pivot in the reporting tool.

Monthly revenue per customer as columns, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). John has no paid orders and appears with empty cells because the join is a LEFT JOIN with the status filter in ON.

SELECT c.name,
       sum(o.amount) FILTER (WHERE o.ordered_on <  '2026-02-01') AS jan,
       sum(o.amount) FILTER (WHERE o.ordered_on >= '2026-02-01'
                               AND o.ordered_on <  '2026-03-01') AS feb,
       sum(o.amount) FILTER (WHERE o.ordered_on >= '2026-03-01') AS mar,
       sum(o.amount) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.name ORDER BY c.name;

Output:

 name  |   jan   |   feb   | mar |  total  
-------+---------+---------+-----+---------
 Asha  | 1500.00 |  700.00 |     | 2200.00
 John  |         |         |     |        
 Meera |         | 2000.00 |     | 2000.00
 Ravi  |         |  450.00 |     |  450.00
(4 rows)

COALESCE empty cells for reports

Wrap each aggregate in COALESCE(..., 0) when a report should show zeros instead of blanks.

Quick check: Why use >= start and < next_start for date ranges?

• BETWEEN is not supported
• They are faster to type
• Half-open ranges avoid gaps and double counting at boundaries
• They ignore time zones