2.2.6.5 GROUPING_ID Function

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.

loc	dname	Bit Vector gf_loc gf_dname	GROUPING_ID gid
BOSTON	OPERATIONS	0 * 21 + 0 * 20	0
BOSTON	null	0 * 21 + 1 * 20	1
null	ACCOUNTING	1 * 21 + 0 * 20	2
null	null	1 * 21 + 1 * 20	3

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

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

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)