Oracle Grouping Function And Grouping_ID In Summary Queries
This is a way to analyze grouped data when grouping more than one column with GROUP BY ROLLUP(col1,col2,..) using GROUPING_ID(col1,col2,..).
Query below shows how to use GROUP BY function with ROLLUP for grouping single column using GROUPING() function in summary queries as shown in example 1 below. This is a feature of Oracle 8i and higher.
Example 1:

Output Of Above Query (Grouping)

Example 1a:
The same can be done in Oracle 9i and higher using GROUPING_ID() as shown below (essentially same result as in example 1):

Output Of Above Query (Grouping_ID)

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, ....

Maximum Value Of Grouping_ID

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:

Output Of Above Query (Grouping_ID)

The result of the summary query with rollup by changing (swapping) the order of the columns and making corresponding GROUPING_ID changes is as shown below. The maximum value of GROUPING_ID is still the same.
Example 2b:
 

Output Of Above Query (Grouping_ID)


Example 3 is a three column GROUP BY query where the GROUPING_ID value of 3 is excluded as shown below
 
Example 3:
 

Output Of Above Query (Grouping_ID)




For corrections or feedback, other useful links please contact webmaster

  1274