The GROUPING function tells us whether or not a null in a result set represents the set of all values produced by a GROUPING SETS, ROLLUP, or CUBE operation. A value of "1" tells us it does, a value of "0" tells us it does not. In the output of the following query two of the four nulls represent the set of all GRP_B values.
set null '(null)' select grp_a , grp_b , count(*) , GROUPING( GRP_A ) GROUPING_GRP_A , GROUPING( GRP_B ) GROUPING_GRP_B from t2 group by grouping sets( (grp_a, grp_b), grp_a ) order by 1 , 2 ;
GRP_A GRP_B COUNT(*) GROUPING_GRP_A GROUPING_GRP_B ---------- ---------- ---------- -------------- -------------- A1 X1 1 0 0 A1 X2 1 0 0 A1 (null) 3 0 0 A1 (null) 5 0 1 A2 (null) 1 0 0 A2 (null) 1 0 1
Of course adding a column with zeros and ones to a report isn't the most user friendly way to distinguish grouped values. However, GROUPING can be used with DECODE to insert labels like "Total" into the result set. Here is one example.
select grp_a as "Group A" , decode ( GROUPING( GRP_B ) , 1, 'Total:' , grp_b ) as "Group B" , count(*) as "Count" from t2 group by grouping sets( (grp_a, grp_b), grp_a ) order by grp_a , GROUPING( GRP_B ) , grp_b ;
Group A Group B Count ---------- ---------- ---------- A1 X1 1 A1 X2 1 A1 (null) 3 A1 Total: 5 A2 (null) 1 A2 Total: 1
Nulls and Aggregate Functions
In this topic we explored working with null values in GROUP BY columns. To learn how aggregate functions like COUNT() and SUM() deal with null values in non-GROUP BY columns see Nulls and Aggregate Functions.
Gotcha - ORA-00979
When using ORDER BY we need to be careful with the selection of column aliases. For example, say we attempted this query.
select grp_a , decode( grouping( grp_b ), 1, 'Total:', grp_b ) AS GRP_B , count(*) from t2 group by grouping sets( (grp_a, grp_b), grp_a ) order by grouping( GRP_B ) ; , decode( grouping( grp_b ), 1, 'Total:', grp_b ) AS GRP_B * ERROR at line 3: ORA-00979: not a GROUP BY expression
Note how the table has a column called GRP_B and the SELECT list has a
column alias also called GRP_B.
In the ORDER BY GROUPING( GRP_B )
clause one might expect
the "GRP_B" term to refer to the table column, but Oracle interprets it as
referring to the column alias, hence the ORA-00979 error.
To work around the error we can either prefix the column name with its table name
select grp_a , decode( grouping( grp_b ), 1, 'Total:', grp_b ) AS GRP_B , count(*) from t2 group by grouping sets( (grp_a, grp_b), grp_a ) order by grouping( T2.GRP_B ) ;
GRP_A GRP_B COUNT(*) ---------- ---------- ---------- A1 (null) 3 A1 X1 1 A1 X2 1 A2 (null) 1 A1 Total: 5 A2 Total: 1
or change the column alias.
select grp_a as "Group A" , decode( grouping( grp_b ), 1, 'Total:', grp_b ) AS "Group B" , count(*) as "Count" from t2 group by grouping sets( (grp_a, grp_b), grp_a ) order by grouping( GRP_B ) ;
Group A Group B Count ---------- ---------- ---------- A1 (null) 3 A1 X1 1 A1 X2 1 A2 (null) 1 A1 Total: 5 A2 Total: 1