> ## 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.

# Rollup

<a id="rollup" />

The *rollup* operation takes *n* number of columns and produces *n + 1*
aggregates.

For example, given a *rollup* on columns `A`, `B`, & `C`, it computes the
requested aggregates for the following combinations of columns:

* `{ABC}` - *unique* `A B C` *triplets*
* `{AB }` - *unique* `A B` *pairs*
* `{A  }` - *unique* `A` *values*
* `{   }` - *all values*

The *rollup* 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 [ROLLUP](/content/sql/query#sql-grouping-rollup) function.  Be mindful
that *rollup* is an expensive operation.

## Grouping and Nulls

If using the *rollup* 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 *rollup* operation.  There will be an example
of this below.

## Example

The following request will aggregate the average opening stock price
for these groups:

* Each market sector & stock symbol pair
* Each market sector
* 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 ROLLUP(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(DECIMAL(Open, 7, 2)) as AvgOpen"
      ],
      options = {"rollup": "(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 *rollup* operation must be listed as columns in the
  `column_names` parameter
* As the *rollup* operation is executed via [/aggregate/groupby](/content/api/rest/aggregate_groupby_rest), all
  [Aggregation Limitations](/content/concepts/aggregation#aggregation-limitations) also apply