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(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.
GROUP BY GROUPING SETS(a, (b), (c, d), ())
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))
GROUP = GROUP BY() = GROUP BY GROUPING SETS(())
GROUP BY GROUPING SETS ((x), (x)) GROUP BY GROUPING SETS ((x)))
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)