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 dimensionsindependently: 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 row there is no single category, so category_name comes back as NULL. Wrap each dimension in coalesce() to give those subtotal rows a readable label like "All categories".