Grouping - Kinetica Docs

The GROUP BY clause can also be used to apply the following grouping functions over the values within each group:

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:

GROUPING Expression Example

CASE
    WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
    ELSE NVL(Sector, '<UNKNOWN SECTOR>')
END AS SectorGroup,

ROLLUP

The ROLLUP(expr list) 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

ROLLUP Example

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

CUBE

The CUBE(expr list) function calculates 2ⁿ 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

CUBE Example

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

GROUPING SETS

The GROUPING SETS(expr list) 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

GROUPING SETS Example

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

​ROLLUP

​CUBE

​GROUPING SETS

ROLLUP

CUBE

GROUPING SETS