-- Created on savesnippets.com · https://savesnippets.com/3yuYSBDTEeiMbz
-- Region + product subtotals, plus a grand total
SELECT
    COALESCE(region,  'TOTAL') AS region,
    COALESCE(product, 'TOTAL') AS product,
    SUM(amount)                AS revenue
FROM   sales
GROUP  BY ROLLUP (region, product)
ORDER  BY region NULLS LAST, product NULLS LAST;

-- Output (illustration):
--   EU   | gizmo  |   500
--   EU   | widget |   800
--   EU   | TOTAL  |  1300    ← subtotal for EU
--   US   | gizmo  |   200
--   US   | widget |  1000
--   US   | TOTAL  |  1200    ← subtotal for US
--   TOTAL| TOTAL  |  2500    ← grand total

-- CUBE = ROLLUP for every dimension combination (more rows)
SELECT region, product, SUM(amount) FROM sales
GROUP BY CUBE (region, product);

-- GROUPING() tells you which row is a subtotal (1) vs a leaf (0)
SELECT region, product, SUM(amount), GROUPING(region) AS is_region_total
FROM sales GROUP BY ROLLUP (region, product);