-- ROLLUP (hierarchical)

SELECT

region,

country,

SUM(sales) AS total_sales

FROM orders

GROUP BY ROLLUP(region, country);

-- CUBE (all combinations)

SELECT

region,

product,

SUM(sales) AS total_sales

FROM orders

GROUP BY CUBE(region, product);

-- GROUPING function

SELECT

region,

country,

SUM(sales) AS total_sales,

GROUPING(region) AS is_region_total,

GROUPING(country) AS is_country_total

FROM orders

GROUP BY ROLLUP(region, country);

-- GROUPING SETS (custom)

SELECT

region,

product,

SUM(sales) AS total_sales

FROM orders

GROUP BY GROUPING SETS (

(region, product),

(region),

()

);

ROLLUP creates hierarchical subtotals right to left

CUBE creates all possible combinations (2^n groups)

GROUPING(column) returns 1 for subtotal, 0 for detail

NULL in result indicates subtotal/grand total row

GROUPING SETS specifies custom combinations

More efficient than UNION of multiple GROUP BY