CUBE extension

CUBE takes a specified set of attributes and creates subtotals for all of their possible combinations.

If n attributes are specified for a CUBE, there will be 2 to the n combinations of subtotals returned.

CUBE (like ROLLUP) is syntactic sugar for GROUPING SETS: 
CUBE(a, b, c) = GROUPING SETS((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ())

CUBE syntax

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

CUBE example

This example is very similar to the ROLLUP example, except that it uses CUBE: 
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 CUBE(Countries, States, OrderMonth)

Partial CUBE

Partial CUBE is similar to partial ROLLUP in that you can limit it to certain attributes and precede it with attributes outside the CUBE operator. In this case, subtotals of all possible combinations are limited to the attributes within the cube list (in parentheses), and they are combined with the preceding items in the GROUP BY list.

The syntax for partial CUBE is: 
GROUP BY expr1, CUBE(expr2, expr3)
This syntax example calculates 2^2 (i.e., 4) subtotals:
  • (expr1, expr2, expr3)
  • (expr1, expr2)
  • (expr1, expr3)
  • (expr1)
Using the above example, the GROUP BY clause for partial CUBE would look like this: 
DEFINE Resellers AS SELECT
  ...
RETURN ResellerSales AS
SELECT SUM(Sales) AS TotalSales
FROM Resellers
GROUP BY Countries, CUBE(States, OrderMonth)