GROUPING_ID function v16
The GROUPING_ID
function provides a simplification 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 an indication of whether a row is a subtotal over all values of the given column. Thus, you might need multiple GROUPING
functions to interpret the level of subtotals for queries with multiple grouping columns.
The GROUPING_ID
function accepts one or more column expressions that were used in the ROLLBACK
, CUBE
, or GROUPING SETS
extensions. It returns a single integer that you can use to determine over which of these columns a subtotal was aggregated.
Syntax
The general syntax for the GROUPING_ID
function is:
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 1s and 0s 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.
Examples
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
:
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
:
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 * 2¹ + 0 * 2⁰ | 0 |
BOSTON | null | 0 * 2¹ + 1 * 2⁰ | 1 |
null | ACCOUNTING | 1 * 2¹ + 0 * 2⁰ | 2 |
null | null | 1 * 2¹ + 1 * 2⁰ | 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 |
To display only those subtotals by dname
, use the following simplified query with a HAVING
clause based on the GROUPING_ID
function:
The result of the query is: