Aggregates and functions for multilevel grouping

Vertica provides several aggregates and functions that group the results of a GROUP BY query at multiple levels.

Vertica provides several aggregates and functions that group the results of a GROUP BY query at multiple levels.

Aggregates for multilevel grouping

Use the following aggregates for multilevel grouping:

  • ROLLUP automatically performs subtotal aggregations. ROLLUP performs one or more aggregations across multiple dimensions, at different levels.

  • CUBE performs the aggregation for all permutations of the CUBE expression that you specify.

  • GROUPING SETS let you specify which groupings of aggregations you need.

You can use CUBE or ROLLUP expressions inside GROUPING SETS expressions. Otherwise, you cannot nest multilevel aggregate expressions.

Grouping functions

You use one of the following three grouping functions with ROLLUP, CUBE, and GROUPING SETS:

  • GROUP_ID returns one or more numbers, starting with zero (0), to uniquely identify duplicate sets.

  • GROUPING_ID produces a unique ID for each grouping combination.

  • GROUPING identifies for each grouping combination whether a column is a part of this grouping. This function also differentiates NULL values in the data from NULL grouping subtotals.

These functions are typically used with multilevel aggregates.