IntermediateGROUP BY & Aggregation

ROLLUP: hierarchical sales totals

The query

SQL

SELECT
  EXTRACT(YEAR FROM o.order_date) AS year,
  EXTRACT(MONTH FROM o.order_date) AS month,
  SUM(o.total) AS revenue
FROM orders o
WHERE status = 'completed'
GROUP BY ROLLUP(
  EXTRACT(YEAR FROM o.order_date),
  EXTRACT(MONTH FROM o.order_date)
)
ORDER BY year NULLS LAST, month NULLS LAST;

Tested against PostgreSQL 16

Note

“ROLLUP creates subtotals at each grouping level. NULL in result = subtotal/grand total row.

Tables referenced

orders6 cols