GROUPING SETS expression

A GROUPING SETS expression allows you to selectively specify the set of groups that you want to create within a GROUP BY clause.

GROUPING SETS specifies multiple groupings of data in one query. Only the specified groups are aggregated, instead of the full set of aggregations that are generated by CUBE or ROLLUP. GROUPING SETS can contain a single element or a list of elements. GROUPING SETS can specify groupings equivalent to those returned by ROLLUP or CUBE.

Note that multiple grouping sets are supported against named states, but are not supported against the corpus.

GROUPING SETS syntax

The GROUPING SETS syntax is:

GROUPING SETS(groupingSetList)

where groupingSetList is a single attribute, a comma-separated list of multiple attributes, CUBE, ROLLUP, or () to specify an empty group. The empty group generates a total. Note that nested grouping sets are not allowed.

For example:

GROUP BY GROUPING SETS(a, (b), (c, d), ())

Multiple grouping sets expressions can exist in the same query.

GROUP BY a, GROUPING SETS(b, c), GROUPING SETS((d, e))

is equivalent to:

GROUP BY GROUPING SETS((a, b, d, e),(a, c, d, e))

Keep in mind that the use of () to specify an empty group means that the following are all equivalent:

GROUP = GROUP BY() = GROUP BY GROUPING SETS(())

Note: Multiple grouping sets cannot be used on the corpus.

How duplicate attributes in a grouping set are handled

Specifying duplicate attributes in a given grouping set will not raise an error, but only one instance of the attribute will be used. For example, these two queries are equivalent:

SELECT SUM(PROD_NAME) AS Products GROUP BY PROD_LIST_PRICE, PROD_LIST_PRICE

SELECT SUM(PROD_NAME) AS Products GROUP BY PROD_LIST_PRICE

However, you can use duplicate attributes if they are in different grouping sets. In this GROUPING SETS example:

GROUP BY GROUPING SETS((COUNTRY_TOTAL), (COUNTRY_TOTAL))

two "COUNTRY_TOTAL" groups are generated.

However, this example:

GROUP BY GROUPING SETS((COUNTRY_TOTAL, COUNTRY_TOTAL))

will generate only one "COUNTRY_TOTAL" group because both attributes are in the same grouping set.

GROUPING SETS example

DEFINE ResellerSales AS
SELECT SUM(DimReseller_AnnualSales) AS TotalSales,
  ARB(DimReseller_ResellerName) AS RepNames,
  DimReseller_OrderMonth AS OrderMonth
GROUP BY OrderMonth;

RETURN MonthlySales AS
SELECT AVG(TotalSales) AS AvgSalesPerRep
FROM ResellerSales
GROUP BY TotalSales, GROUPING SETS(RepNames), GROUPING SETS(OrderMonth)