GROUPING_ID function v16
GROUPING_ID function provides a simplification of the
GROUPING function to determine the subtotal level of a row in the result set from a
GROUPING SETS extension.
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.
GROUPING_ID function accepts one or more column expressions that were used in the
GROUPING SETS extensions. It returns a single integer that you can use to determine over which of these columns a subtotal was aggregated.
The general syntax for the
GROUPING_ID function is:
GROUPING_ID function takes one or more parameters that must be expressions of dimension columns specified in the expression list of a
GROUPING SETS extension of the
GROUP BY clause.
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
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
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
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 * 2¹ + 0 * 2⁰
|0 * 2¹ + 1 * 2⁰
|1 * 2¹ + 0 * 2⁰
|1 * 2¹ + 1 * 2⁰
The following table summarizes how the
GROUPING_ID function return values correspond to the grouping columns over which aggregation occurs.
|Aggregation by Column
To display only those subtotals by
dname, use the following simplified query with a
HAVING clause based on the
The result of the query is: