ROLLUP

ROLLUP (a, b) is shorthand for the grouping sets (a, b), (a), (), suited to hierarchies such as year and quarter. MySQL 8.4 524 has WITH ROLLUP too; PostgreSQL 1,289 also mixes rollups, cubes and plain columns in one GROUP BY.

SELECT extract(year FROM order_date)::int AS year,
       extract(quarter FROM order_date)::int AS qtr,
       sum(gross_amount) AS gross, count(DISTINCT order_id) AS orders
FROM mart.sales
WHERE status <> 'cancelled'
GROUP BY ROLLUP (1, 2)
ORDER BY 1, 2;

 year | qtr |   gross    | orders
------+-----+------------+--------
 2025 |   1 |  549249.78 |  15496
 ...
 2025 |   4 |  553383.76 |  15778
 2025 |     | 2219580.47 |  62820
 2026 |   1 |  547701.45 |  15704
 2026 |   2 |  536145.38 |  15586
 2026 |     | 1083846.83 |  31290
      |     | 3303427.30 |  94110

ROLLUP (1, 2) names the first two output columns. Subtotals are computed from the detail rows, not by adding the lines above, so count(DISTINCT order_id) stays correct at every level. Order matters: (qtr, year) would subtotal each quarter number across years.