GROUPING_ID Function v13

The GROUPING_ID function provides a simplification of the GROUPING function in order 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 an indication of whether or not a row is a subtotal over all values of the given column. Thus, multiple GROUPING functions may be required to interpret the level of subtotals for queries with multiple grouping columns.

The GROUPING_ID function accepts one or more column expressions that have been used in the ROLLBACK, CUBE, or GROUPING SETS extensions and returns a single integer that can be used to determine over which of these columns a subtotal has been aggregated.

The general syntax for use of the GROUPING_ID function is shown by the following.

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 takes 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 consisting of the concatenated 1’s and 0’s that would be 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 returned values of the GROUPING_ID function represented in column gid correspond to the values returned by two GROUPING functions on columns loc and dname.

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;

In the following output, note the relationship between a bit vector consisting of the gf_loc value and gf_dname value compared to the integer given in gid.

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 based on the GROUPING function return values for four rows of the output.

The following table summarizes how the GROUPING_ID function return values correspond to the grouping columns over which aggregation occurs.

So 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 is the result of the 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)