Basic ROLLUP

intermediate

Sales report with region and country subtotals plus grand total

SELECT

region,

country,

SUM(sales) AS total_sales

FROM orders

GROUP BY ROLLUP(region, country)

ORDER BY region NULLS LAST, country NULLS LAST;

region | country | total_sales

EMEA | UK | 50000

EMEA | France | 30000

EMEA | NULL | 80000 -- EMEA subtotal

APAC | Japan | 40000

APAC | NULL | 40000 -- APAC subtotal

NULL | NULL | 120000 -- Grand total

ROLLUP creates subtotals at each level. NULL indicates subtotal row. Grand total at bottom with all NULLs.

PostgreSQL

CUBE for Multi-Dimensional Analysis

advanced

Analyze sales by region AND product with all combinations

SELECT

region,

product,

SUM(sales) AS total_sales

FROM orders

GROUP BY CUBE(region, product)

ORDER BY region NULLS LAST, product NULLS LAST;

region | product | total_sales

EMEA | Laptop | 30000

EMEA | Mouse | 20000

EMEA | NULL | 50000 -- EMEA total (all products)

APAC | Laptop | 25000

APAC | NULL | 25000 -- APAC total

NULL | Laptop | 55000 -- Laptop total (all regions)

NULL | Mouse | 20000 -- Mouse total

NULL | NULL | 75000 -- Grand total

CUBE creates all combinations: by region, by product, and grand total. More rows than ROLLUP.

PostgreSQL