The GROUPING_ID function simplifies the implementation of the GROUPING function to determine the subtotal level of a row in the result set from a ROLLBACK, CUBE, or GROUPING SETS extension.

The GROUPING function takes only one column expression and returns a value to indicate whether a row is a subtotal over all values of the specified column. Multiple GROUPING functions may be required to interpret the level of subtotals for queries with multiple grouping columns.

The GROUPING_ID function supports one or more column expressions that have been used in the ROLLBACK, CUBE, or GROUPING SETS extensions and returns a single integer that indicates the column on which a subtotal has been aggregated.

The GROUPING_ID function has the following general syntax:

SELECT [ expr ...,]
  GROUPING_ID( col_expr_1 [, col_expr_2 ] ... )
  [, expr ] ...
FROM ...
GROUP BY [...,]
  { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr_1
  [, col_expr_2 ] [, ...] ) [, ...]

The GROUPING_ID function uses one or more parameters that must be expressions of dimension columns specified in the expression list of a ROLLUP, CUBE, or GROUPING SETS extension of the GROUP BY clause.

The GROUPING_ID function returns an integer value. This value corresponds to the base-10 interpretation of a bit vector that consists of concatenated 1s and 0s. This bit vector is returned by a series of GROUPING functions specified in the same left-to-right order as the ordering of the parameters specified in the GROUPING_ID function.

The following query shows how the values in column gid returned by the GROUPING_ID function correspond to the values in columns loc and dname returned by two GROUPING functions.

SELECT loc, dname, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc", GROUPING(dname) AS "gf_dname",
  GROUPING_ID(loc, dname) AS "gid"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname)
ORDER BY 6, 1, 2;

The following output shows the relationship between a bit vector and an integer specified in gid. The bit vector consists of the gf_loc value and the gf_dname value.

   loc    |   dname    | employees | gf_loc | gf_dname | gid
----------+------------+-----------+--------+----------+-----
 BOSTON   | OPERATIONS |         3 |      0 |        0 |   0
 BOSTON   | RESEARCH   |         5 |      0 |        0 |   0
 CHICAGO  | SALES      |         6 |      0 |        0 |   0
 NEW YORK | ACCOUNTING |         3 |      0 |        0 |   0
 BOSTON   |            |         8 |      0 |        1 |   1
 CHICAGO  |            |         6 |      0 |        1 |   1
 NEW YORK |            |         3 |      0 |        1 |   1
          | ACCOUNTING |         3 |      1 |        0 |   2
          | OPERATIONS |         3 |      1 |        0 |   2
          | RESEARCH   |         5 |      1 |        0 |   2
          | SALES      |         6 |      1 |        0 |   2
          |            |        17 |      1 |        1 |   3
(12 rows)

The following table provides specific examples of the GROUPING_ID function calculations. These calculations are based on four row values returned by the GROUPING function in the output.

loc dname Bit Vector

gf_loc gf_dname

GROUPING_ID

gid

BOSTON OPERATIONS 0 * 2 1 + 0 * 2 0 0
BOSTON null 0 * 2 1 + 1 * 2 0 1
null ACCOUNTING 1 * 2 1 + 0 * 2 0 2
null null 1 * 2 1 + 1 * 2 0 3

The following table summarizes how the values returned by the GROUPING_ID function correspond to the grouping columns to be aggregated.

Aggregation by column Bit vector

gf_loc gf_dname

GROUPING_ID

gid

loc, dname 0 0 0
loc 0 1 1
dname 1 0 2
Grand Total 1 1 3

To display only those subtotals by dname, the following simplified query can be used with a HAVING clause based on the GROUPING_ID function.

SELECT loc, dname, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc", GROUPING(dname) AS "gf_dname",
  GROUPING_ID(loc, dname) AS "gid"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname)
HAVING GROUPING_ID(loc, dname) = 2
ORDER BY 6, 1, 2;

The following example shows the result of this query:

loc |   dname    | employees | gf_loc | gf_dname | gid
-----+------------+-----------+--------+----------+-----
     | ACCOUNTING |         3 |      1 |        0 |   2
     | OPERATIONS |         3 |      1 |        0 |   2
     | RESEARCH   |         5 |      1 |        0 |   2
     | SALES      |         6 |      1 |        0 |   2
(4 rows)