GROUPING_ID

Concatenates the set of Boolean values generated by the GROUPING function into a bit vector. GROUPING_ID treats the bit vector as a binary number and returns it as a base-10 value that identifies the grouping set combination.

By using GROUPING_ID you avoid the need for multiple, individual GROUPING functions. GROUPING_ID simplifies row-filtering conditions, because rows of interest are identified using a single return from GROUPING_ID = n. Use GROUPING_ID to identify grouping combinations.

Use GROUPING_ID only in SELECT statements that contain a GROUP BY aggregate: CUBE, GROUPING SETS, and ROLLUP.

Behavior Type

Immutable

Syntax

GROUPING_ID ( [expression[,…] )
expression

An expression that matches one of the expressions in the GROUP BY clause.

If the GROUP BY clause includes a list of expressions, GROUPING_ID returns a number corresponding to the GROUPING bit vector associated with a row.

Examples

This example shows how calling GROUPING_ID without an expression returns the GROUPING bit vector associated with a full set of multilevel aggregate expressions. The GROUPING_ID value is comparable to GROUPING_ID(a,b) because GROUPING_ID() includes all columns in the GROUP BY ROLLUP: 

=> SELECT a,b,COUNT(*), GROUPING_ID() FROM T GROUP BY ROLLUP(a,b);

In the following query, the GROUPING(Category) and GROUPING(Year) columns have three combinations:

  • 0,0
  • 0,1
  • 1,1
=> 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

GROUPING_ID converts these values as follows: 

Binary Set Values Decimal Equivalents
00 0
01 1
11 3
0 Category, Year

The following query returns the single number for each GROUP BY level that appears in the gr_id column:

=> SELECT Category, Year, SUM(Amount), 
   GROUPING(Category),GROUPING(Year),GROUPING_ID(Category,Year) AS gr_id 
   FROM expenses GROUP BY ROLLUP(Category, Year);
  Category   | Year |  SUM   | GROUPING | GROUPING | gr_id
-------------+------+--------+----------+----------+-------
 Books       | 2008 |  29.99 |        0 |        0 |     0
 Books       | 2005 |  39.98 |        0 |        0 |     0
 Electricity | 2007 | 229.98 |        0 |        0 |     0
 Books       | 2007 |  29.99 |        0 |        0 |     0
 Electricity | 2005 | 109.99 |        0 |        0 |     0
 Electricity |      | 449.96 |        0 |        1 |     1
             |      | 549.92 |        1 |        1 |     3
 Electricity | 2006 | 109.99 |        0 |        0 |     0
 Books       |      |  99.96 |        0 |        1 |     1

The gr_id value determines the GROUP BY level for each row:

GROUP BY Level GROUP BY Row Level
3 Total sum
1 Category
0 Category, year

You can also use the DECODE function to give the values more meaning by comparing each search value individually: 

=> SELECT Category, Year, SUM(AMOUNT), DECODE(GROUPING_ID(Category, Year),
       3, 'Total',
       1, 'Category',
       0, 'Category,Year') 
   AS GROUP_NAME FROM expenses GROUP BY ROLLUP(Category, Year);
  Category   | Year |  SUM   |  GROUP_NAME
-------------+------+--------+---------------
 Electricity | 2006 | 109.99 | Category,Year
 Books       |      |  99.96 | Category
 Electricity | 2007 | 229.98 | Category,Year
 Books       | 2007 |  29.99 | Category,Year
 Electricity | 2005 | 109.99 | Category,Year
 Electricity |      | 449.96 | Category
             |      | 549.92 | Total
 Books       | 2005 |  39.98 | Category,Year
 Books       | 2008 |  29.99 | Category,Year

See Also