grouping_id function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the level of grouping for a set of columns.

Syntax 

grouping_id( [col1 [, ...] ] )

Arguments

  • colN: A column reference identified in a GROUPING SET, ROLLUP, or CUBE.

Returns

A BIGINT.

The function combines the grouping function for several columns into one by assigning each column a bit in a bit vector. The col1 is represented by the highest order bit. A bit is set to 1 if the row computes a subtotal for the corresponding column.

Specifying no argument is equivalent to specifying all columns listed in the GROUPING SET, CUBE, or ROLLUP.

Examples 

> SELECT name, age, grouping_id(name, age),
         conv(cast(grouping_id(name, age) AS STRING), 10, 2),
         avg(height)
    FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height)
  GROUP BY cube(name, age)
Alice 2    0    0     165.0
Alice NULL 1    1     165.0
NULL  2    2   10     165.0
NULL  NULL 3   11     172.5
Bob   NULL 1    1     180.0
Bob   5    0    0     180.0
NULL  5    2   10     180.0