In the preceding topic we saw how the GROUPING function could be used to identify null values representing the set of all values produced by a GROUPING SETS, ROLLUP, or CUBE operation. What if we wanted to distinguish entire rows from each other? We could use a number of different GROUPING() calls like this
column bit_vector format a10
select
TO_CHAR( GROUPING( GRP_A ) )
|| TO_CHAR( GROUPING( GRP_B ) )
AS BIT_VECTOR
,
DECODE
( TO_CHAR( GROUPING( GRP_A ) )
|| TO_CHAR( GROUPING( GRP_B ) )
, '01', 'Group "' || GRP_A || '" Total'
, '10', 'Group "' || GRP_B || '" Total'
, '11', 'Grand Total'
, NULL
) AS LABEL
,
count(*)
from
t2
group by
grouping sets ( grp_a, grp_b, () )
order by
GROUPING( GRP_A )
, grp_a
, GROUPING( GRP_B )
, grp_b
;
BIT_VECTOR LABEL COUNT(*) ---------- ------------------------ ---------- 01 Group "A1" Total 5 01 Group "A2" Total 1 10 Group "X1" Total 1 10 Group "X2" Total 1 10 Group "" Total 4 11 Grand Total 6
but if the number of grouping sets were large concatenating all the required GROUPING() terms together would get cumbersome. Fortunately for us the GROUPING_ID function exists. It yields the decimal value of a bit vector (a string of zeros and ones) formed by concatenating all the GROUPING values for its parameters. The following example shows how it works.
select to_char( grouping( grp_a ) ) || to_char( grouping( grp_b ) ) as bit_vector -- this column is only included for clarity , GROUPING_ID( GRP_A, GRP_B ) , grp_a , grp_b , count(*) from t2 group by grouping sets ( grp_a, grp_b, () ) order by GROUPING_ID( GRP_A, GRP_B ) , grp_a , grp_b ;
BIT_VECTOR GROUPING_ID(GRP_A,GRP_B) GRP_A GRP_B COUNT(*) ---------- ------------------------ ---------- ---------- ---------- 01 1 A1 5 01 1 A2 1 10 2 X1 1 10 2 X2 1 10 2 4 11 3 6
Here is how we could use GROUPING_ID to streamline our original query.
select DECODE ( GROUPING_ID( GRP_A, GRP_B ) , 1, 'Group "' || GRP_A || '" Total' , 2, 'Group "' || GRP_B || '" Total' , 3, 'Grand Total' , NULL ) AS LABEL , count(*) from t2 group by grouping sets ( grp_a, grp_b, () ) order by GROUPING_ID( GRP_A, GRP_B ) , grp_a , grp_b ;
LABEL COUNT(*) ------------------------ ---------- Group "A1" Total 5 Group "A2" Total 1 Group "X1" Total 1 Group "X2" Total 1 Group "" Total 4 Grand Total 6
Composite Columns
The following example shows how GROUPING_ID works when a composite column, (GRP_A, GRP_B), is included in the GROUPING SETS clause.
select GROUPING_ID( GRP_A, GRP_B ) , grp_a , grp_b , count(*) from t2 group by grouping sets ( (grp_a, grp_b), grp_a, grp_b, () ) order by 1 , 2 , 3 ;
GROUPING_ID(GRP_A,GRP_B) GRP_A GRP_B COUNT(*)
------------------------ ---------- ---------- ----------
0 A1 X1 1
0 A1 X2 1
0 A1 3
0 A2 1
1 A1 5
1 A2 1
2 X1 1
2 X2 1
2 4
3 6
GROUPING_ID and HAVING
GROUPING_ID can also be used in the HAVING clause to filter out unwanted groupings. Say, for example, we started with a query like this one
select grouping_id( grp_a, grp_b ) , grp_a , grp_b , count(*) from t2 group by cube( grp_a, grp_b ) order by 1, 2, 3 ;
GROUPING_ID(GRP_A,GRP_B) GRP_A GRP_B COUNT(*)
------------------------ ---------- ---------- ----------
0 A1 X1 1
0 A1 X2 1
0 A1 3
0 A2 1
1 A1 5
1 A2 1
2 X1 1
2 X2 1
2 4
3 6
and then we wanted to exclude the empty set grouping (the one with a GROUPING_ID of "3"). We simply add a HAVING clause as follows.
select grouping_id( grp_a, grp_b ) , grp_a , grp_b , count(*) from t2 group by cube( grp_a, grp_b ) HAVING GROUPING_ID( GRP_A, GRP_B ) != 3 order by 1, 2, 3 ;
GROUPING_ID(GRP_A,GRP_B) GRP_A GRP_B COUNT(*)
------------------------ ---------- ---------- ----------
0 A1 X1 1
0 A1 X2 1
0 A1 3
0 A2 1
1 A1 5
1 A2 1
2 X1 1
2 X2 1
2 4