GROUPING
Disambiguates the use of NULL
values when GROUP BY
queries with multilevel aggregates generate NULL values to identify subtotals in grouping columns. Such NULL
values from the original data can also occur in rows. GROUPING
returns 1, if the value of expression is:
NULL
, representing an aggregated value- 0 for any other value, including
NULL
values in rows
Use GROUPING
only in SELECT
statements that contain a GROUP BY
aggregate: CUBE
, GROUPING SETS
, and ROLLUP
.
Behavior Type
Syntax
GROUPING ( expression )
Parameters
expression |
An expression in the |
Examples
The following query uses the GROUPING
function, taking one of the GROUP BY
expressions as an argument. For each row, GROUPING
returns one of the following:
0
: The column is part of the group for that row1
: The column is not part of the group for that row
The 1 in the GROUPING(Year)
column for electricity and books indicates that these values are subtotals. The right-most column values for both GROUPING(Category)
and GROUPING(Year)
are 1
. This value indicates that neither column contributed to the GROUP BY
. The final row represents the total sales.
=> SELECT Category, Year, SUM(Amount), GROUPING(Category), GROUPING(Year) FROM expenses GROUP BY ROLLUP(Category, Year) ORDER BY Category, Year, GROUPING_ID(); Category | Year | SUM | GROUPING | GROUPING -------------+------+--------+----------+---------- Books | 2005 | 39.98 | 0 | 0 Books | 2007 | 29.99 | 0 | 0 Books | 2008 | 29.99 | 0 | 0 Books | | 99.96 | 0 | 1 Electricity | 2005 | 109.99 | 0 | 0 Electricity | 2006 | 109.99 | 0 | 0 Electricity | 2007 | 229.98 | 0 | 0 Electricity | | 449.96 | 0 | 1 | | 549.92 | 1 | 1
See Also