GROUPING SETS and CUBE in SQL

ROLLUP builds a hierarchy of subtotals: category, then category plus supplier, then a grand total. But sometimes you want totals for two dimensions independently: a breakdown by category and a separate breakdown by supplier without the nested combinations.

GROUPING SETS gives you exactly that control: you list each grouping level you want, and the empty set () adds the grand total. CUBE is the shortcut for "every combination of these dimensions".

select region, product, sum(amount) as total
from sales
group by grouping sets ((region), (product), ())
-- ROLLUP(region, product) = a hierarchy of subtotals
-- CUBE(region, product)   = every combination of subtotals

Labeling the subtotal rows

On a per-supplier subtotal row there is no single category, so category_name comes back as NULL. If source data can also contain real nulls, coalesce() alone cannot tell them apart. Use grouping() to identify generated subtotal rows before assigning labels such as "All categories".