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.

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(())

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 because duplicate grouping set instances are discarded. For example, these two queries are equivalent:

GROUP BY GROUPING SETS ((x), (x))
GROUP BY GROUPING SETS ((x)))

GROUPING SETS example

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

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