Search code examples
data-warehouserollup

Data warehouse rollup and grouping sets, which to use?

I have learned rollup, cube & grouping sets but one thing confuses me is how do I know which to use. For example, if I need to find the sale for each month in 2006 by region & by manager the two queries follow

SELECT month, region, sales_mgr, SUM(price)
FROM Sales
WHERE year = 1996
GROUP BY GROUPING SETS((month, region),(month, sales_mgr))

and

SELECT month, region, sales_mgr, SUM(price)
FROM Sales
WHERE year = 1996
GROUP BY ROLLUP(month, region, sales_mgr)

I know the result of each one but I don't know which to use to answer the question properly, is there something I missed or are both considered correct?

Solution

  • ROLLUP and CUBE are just shorthand for two common usages of GROUPING SETS.

    GROUPING SETS gives more precise control of which aggregations you want to calculate.