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)