> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Grouping Sets

The *grouping sets* operation calculates aggregates over any user-specified
dimensions.  It offers much more flexibility and customizability over the
[Cube](/content/concepts/cube) and [Rollup](/content/concepts/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](/content/api/rest/aggregate_groupby_rest) endpoint.
It is also available via the SQL [GROUPING SETS](/content/sql/query#sql-grouping-groupingsets) 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](/content/concepts/nulls), 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()](/content/concepts/expressions#aggregate-expressions-label)
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

<CodeGroup>
  ```sql SQL theme={null}
  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
  ```

  ```python Python theme={null}
  kinetica.aggregate_group_by(
      table_name = "demo.stocks",
      column_names = [
          "Sector",
          "Symbol",
          "IF(Grouping(Sector) = 0, Sector, '<ALL SECTORS>') as SectorGroup",
          "IF(Grouping(Symbol) = 0, Symbol, '<ALL SYMBOLS>') as SymbolGroup",
          "AVG(Open) as AvgOpen"
      ],
      options = {"grouping_sets": "((Sector), (Symbol), ())"}
  )
  ```
</CodeGroup>

## 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](/content/api/rest/aggregate_groupby_rest),
  all [Aggregation Limitations](/content/concepts/aggregation#aggregation-limitations) also apply