Skip to main content
The grouping sets operation calculates aggregates over any user-specified dimensions. It offers much more flexibility and customizability over the Cube and Rollup functions, while allowing both as possible dimensions over which aggregation can be performed. For example, given grouping sets of B, (D,E), & (ROLLUP(A,C)), it computes the requested aggregates for the following combinations of columns:
  • Group B:
    • { B } - unique B values
  • Group (D,E):
    • { DE} - unique D E pairs
  • Group ROLLUP(A,C):
    • {A C } - unique A C pairs
    • {A } - unique A values
    • { } - all values
The grouping sets operation is an aggregate function that can be invoked natively in the options map of the /aggregate/groupby endpoint. It is also available via the SQL GROUPING SETS function. Be mindful that grouping sets is an expensive operation.

​
Grouping and Nulls

If using the grouping sets operation with a column that contains null values, it may not be apparent which aggregations pertain to the column’s null values and which pertain to all the column’s values, as both will be represented by null in the result set. To avoid this confusion, it’s recommended to use the GROUPING() function to make a distinction between null values in the data and null grouping values generated by the grouping sets operation. There will be an example of this below.

​
Examples

The following request will aggregate the average opening stock price for these groups:
  • Each market sector
  • Each stock symbol
  • All sectors and symbols
SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS SectorGroup,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS SymbolGroup,
    DECIMAL(AVG("Open"), 7, 2) AS AvgOpen
FROM demo.Stocks
GROUP BY GROUPING SETS((Sector), (Symbol), ())
ORDER BY SectorGroup, SymbolGroup

​
Limitations

  • The maximum number of dimensions that can be computed is 256; the maximum number of columns that can be aggregated is 61
  • The column(s) used in the grouping sets operation must be listed as columns in the column_names parameter
  • As the grouping sets operation is executed via /aggregate/groupby, all Aggregation Limitations also apply