SQL Snippets ™: Tutorials for Oracle Developers

In the examples used thus far in the tutorial our base table had no null values in it. Let's now look at grouping a table that does contain null values.

set null '(null)'

select *
from   t2
order by grp_a, grp_b ;
 

GRP_A      GRP_B             VAL
---------- ---------- ----------
A1         X1                 10
A1         X2                 40
A1         (null)             20
A1         (null)             30
A1         (null)             50
A2         (null)             60
 

Now consider the following GROUP BY query.

select grp_a, grp_b, count(*)
from   t2
group by grp_a, grp_b
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
A1         X1                  1
A1         X2                  1
A1         (null)              3
A2         (null)              1
 

So far so good, but let's use GROUPING SETS next and see what happens.

select grp_a, grp_b, count(*)
from   t2
GROUP BY GROUPING SETS( (GRP_A, GRP_B), GRP_A )
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
A1         X1                  1
A1         X2                  1
A1         (null)              3
A1         (null)              5
A2         (null)              1
A2         (null)              1
 

We now have two rows with "(null)" under GRP_B for each GRP_A value, one representing the null values stored in T2.GRP_B and the other representing the set of all values in T2.GRP_B.

Gotcha - NVL and NVL2

One might expect that NVL() or NVL2 could be used to distinguish the two nulls, like this

select
  grp_a
, NVL( t2.GRP_B, 'n/a' ) AS GRP_B
, nvl2( t2.grp_b, 1, 0 ) as test
, count(*)
from   t2
GROUP BY GROUPING SETS( (GRP_A, GRP_B), GRP_A )
order by grp_a, grp_b ;
 

GRP_A      GRP_B            TEST   COUNT(*)
---------- ---------- ---------- ----------
A1         X1                  1          1
A1         X2                  1          1
A1         n/a                 0          5
A1         n/a                 0          3
A2         n/a                 0          1
A2         n/a                 0          1
 

but this is not the case because functions in the SELECT list operate on an intermediate form of the result set created after the GROUP BY clause is applied, not before. In the next topic we see how the GROUPING function can help us distinguish the two types of nulls.