Query shown in Example 2 shows how to use GROUPING_ID function with
ROLLUP (also can be CUBE) for grouping more than one column and to get
subtotals for different grouped columns (rollup values). GROUPING_ID
is a feature in Oracle 9i and higher. GROUPING_ID returns a number
corresponding to the GROUPING bit vector associated with a row (per
Oracle documentation).
|
|
Mathematically the maximum value of GROUPING_ID(column1,column2,.. etc) can
be derived as
|
Max. value of Grouping_ID(n) = 2n - 1
|
Where n is the number of columns being grouped. The maximum value of
Grouping_ID for a query with single column (n = 1) GROUP BY with ROLLUP (or
CUBE) is 1, for two column (n = 2) GROUP BY with ROLLUP it is
3, for three column (n = 3) GROUP BY with ROLLUP it is 7 etc. Thus the
maximum value of GROUPING_ID is a numeric series of
|
1, 3, 7, 15, 31, 63, 127, 255, 511, ....
|
where each number represents the maximum value of GROUPING_ID
of a query that is summarized for single, two column, three
column, ... respectively with a GROUP BY ROLLUP(col1, col2)
etc. The maximum value of GROUPING_ID corresponds to rollup
value or total of all the grouped columns.
|
|
The subtotals and total can be analyzed at various levels by knowing
the different GROUPING_IDs. Also subtotals/summary levels can be
skipped where not necessary by using a
HAVING GROUPING_ID(col1,col2,..) <> 3, 7 etc. as shown in
example 3.
|
|
Example below shows a query that uses two column GROUP BY ROLLUP(col1,col2).
|
Example 2a: |
|
Result of Example 2a: |