GROUPING | Snowflake Documentation (original) (raw)

Categories:

Aggregate functions (General)

Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.

Aliases:

GROUPING_ID

Syntax¶

GROUPING( [ , , ... ] )

Usage notes¶

GROUPING is not an aggregate function, but rather a utility function that can be used alongside aggregation, to determine the level of aggregation a row was generated for:

• GROUPING(_expr_) returns 0 for a row that is grouped on _expr_, and 1 for a row that is not grouped on _expr_.
• GROUPING(_expr1_, _expr2_ , … , _exprN_) returns the integer representation of a bit-vector containing GROUPING(_expr1_) , GROUPING(_expr2_) , … , GROUPING(_exprN_).

Examples¶

Group by sets:

Create and populate a table with values:

CREATE OR REPLACE TABLE aggr2(col_x int, col_y int, col_z int); INSERT INTO aggr2 VALUES(1, 2, 1), (1, 2, 3); INSERT INTO aggr2 VALUES(2, 1, 10), (2, 2, 11), (2, 2, 3);

Show the values in the table:

SELECT * FROM aggr2 ORDER BY col_x, col_y, col_z; +-------+-------+-------+ | COL_X | COL_Y | COL_Z | |-------+-------+-------| | 1 | 2 | 1 | | 1 | 2 | 3 | | 2 | 1 | 10 | | 2 | 2 | 3 | | 2 | 2 | 11 | +-------+-------+-------+

Output:

SELECT col_x, col_y, sum(col_z), grouping(col_x), grouping(col_y), grouping(col_x, col_y) FROM aggr2 GROUP BY GROUPING SETS ((col_x), (col_y), ()) ORDER BY 1, 2; +-------+-------+------------+-----------------+-----------------+------------------------+ | COL_X | COL_Y | SUM(COL_Z) | GROUPING(COL_X) | GROUPING(COL_Y) | GROUPING(COL_X, COL_Y) | |-------+-------+------------+-----------------+-----------------+------------------------| | 1 | NULL | 4 | 0 | 1 | 1 | | 2 | NULL | 24 | 0 | 1 | 1 | | NULL | 1 | 10 | 1 | 0 | 2 | | NULL | 2 | 18 | 1 | 0 | 2 | | NULL | NULL | 28 | 1 | 1 | 3 | +-------+-------+------------+-----------------+-----------------+------------------------+