ROLLUPとCUBEで小計と合計

-- 地域と商品ごとの売上に、地域の小計と総合計を付けます。
select region, product, sum(amount) as total
from region_sales
group by rollup (region, product)
order by region nulls last, product nulls last;
-- 結果の NULL は、「その列についての合計」を表します。

-- cube は、列のすべての組み合わせの合計を出します。
-- grouping は、その行が合計の行なら 1 を返します。
select region, product, sum(amount) as total,
       grouping(region) as g_region,
       grouping(product) as g_product
from region_sales
group by cube (region, product)
order by region nulls last, product nulls last;

-- filter を使って、商品を横に並べた表を手で作ります。
select region,
       sum(amount) filter (where product = '弁当') as bento,
       sum(amount) filter (where product = '飲み物') as drinks,
       sum(amount) as total
from region_sales
group by region
order by region;