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

Immutable

Syntax

GROUPING ( expression )

Parameters

expression

An expression in the GROUP BY clause

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 row
  • 1: 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