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

<a id="sql-grouping" />

The `GROUP BY` clause can also be used to apply the following
[grouping functions](/content/sql/query/grouping-functions#sql-grouping-functions) over the values within each
group:

* [ROLLUP](/content/sql/query/grouping#sql-rollup)
* [CUBE](/content/sql/query/grouping#sql-cube)
* [GROUPING SETS](/content/sql/query/grouping#sql-grouping-sets)

With each of these, the `GROUPING()` aggregate function can be used to
distinguish aggregated *null* values in the data from *null* values generated by
the `ROLLUP`, `CUBE`, or `GROUPING SETS` *grouping function*.

For instance, the following `CASE` will turn the aggregated *null* values in
the `Sector` column into an `<UNKNOWN SECTOR>` group and the *null* value
generated by the *grouping function* into an `<ALL SECTORS>` group:

```sql GROUPING Expression Example theme={null}
CASE
    WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
    ELSE NVL(Sector, '<UNKNOWN SECTOR>')
END AS SectorGroup,
```

<a id="sql-grouping-rollup" />

<a id="sql-rollup" />

## ROLLUP

The [ROLLUP(expr list)](/content/concepts/rollup) function calculates *n* + 1
aggregates for *n* number of columns in `expr list`.

For example, the following query will aggregate the average opening stock price
for these groups:

* Each market sector & stock symbol pair
* Each market sector
* All sectors and symbols

```sql ROLLUP Example 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
```

<a id="sql-grouping-cube" />

<a id="sql-cube" />

## CUBE

The [CUBE(expr list)](/content/concepts/cube) function calculates 2<sup>n</sup>
aggregates for *n* number of columns in `expr list`.

For example, the following query will aggregate the average opening stock price
for these groups:

* Each market sector & stock symbol pair
* Each market sector
* Each stock symbol
* All sectors and symbols

```sql CUBE Example 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 CUBE(Sector, Symbol)
ORDER BY SectorGroup, SymbolGroup
```

<a id="sql-grouping-groupingsets" />

<a id="sql-grouping-sets" />

## GROUPING SETS

The [GROUPING SETS(expr list)](/content/concepts/grouping_sets) function
calculates aggregates for each group of columns in `expr list`.

For example, the following query will aggregate the average opening stock price
for these groups:

* Each market sector
* Each stock symbol
* All sectors and symbols

```sql GROUPING SETS Example 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
```