SQL Snippets ™: Tutorials for Oracle Developers

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