ROLLUP extension

ROLLUP is an extension to GROUP BY that enables calculation of multiple levels of subtotals across a specified group of attributes. It also calculates a grand total.

ROLLUP (like CUBE) is syntactic sugar for GROUPING SETS: 
ROLLUP(a, b, c) = GROUPING SETS((a,b,c), (a,b), (a), ())
The action of ROLLUP is that it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of attributes and works as follows:
  1. It calculates the standard aggregate values specified in the GROUP BY clause.
  2. It creates progressively higher-level subtotals, moving from right to left through the list of attributes.
  3. It creates a grand total.
  4. Finally, ROLLUP creates subtotals at n+1 levels, where n is the number of attributes.

    For instance, if a query specifies ROLLUP on attributes of time, region, and department (n=3), the result set will include rows at four aggregation levels.

In summary, ROLLUP is intended for use in tasks involving subtotals.

ROLLUP syntax

ROLLUP appears in the GROUP BY clause, using this syntax: 
GROUP BY ROLLUP(attributeList)
where attributeList is either a single attribute or a comma-separated list of multiple attributes. The attributes may be single-assign or multi-assign attributes. ROLLUP can be used on collections.

ROLLUP example

DEFINE Resellers AS SELECT
  DimReseller_AnnualSales AS Sales,
  DimGeography_CountryRegionName AS Countries,
  DimGeography_StateProvinceName AS States,
  DimReseller_OrderMonth AS OrderMonth
FROM ResellerState
WHERE DimReseller_OrderMonth IS NOT NULL;

RETURN ResellerSales AS
SELECT SUM(Sales) AS TotalSales
FROM Resellers
GROUP BY ROLLUP(Countries, States, OrderMonth)

Partial ROLLUP

You can also roll up so that only some of the subtotals are included. This partial rollup uses this syntax: 
GROUP BY expr1, ROLLUP(expr2, expr3)

In this case, the GROUP BY clause creates subtotals at (2+1=3) aggregation levels. That is, at level (expr1, expr2, expr3), (expr1, expr2), and (expr1).

Using the above example, the GROUP BY clause for partial ROLLUP would look like this: 
DEFINE Resellers AS SELECT
  ...

RETURN ResellerSales AS
SELECT SUM(Sales) AS TotalSales
FROM Resellers
GROUP BY Countries, ROLLUP(States, OrderMonth)