2.2.6.5 GROUPING_ID Function

Table of Contents Previous Next



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 ] ...
[, 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.
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.
0 * 21 + 0 * 20
0 * 21 + 1 * 20
1 * 21 + 0 * 20
1 * 21 + 1 * 20
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.


Table of Contents Previous Next