SQL Snippets ™: Tutorials for Oracle Developers

When I first started using GROUPING SETS I used constants to produce grand total rows in my result sets, like this.

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

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1                             5
a2                             3
           b1                  2
           b2                  3
           b3                  3
                               8
 

The last row in the result set is generated by the "0" grouping. I later learnt that an empty set term, "()", was actually a more appropriate syntactic choice than a constant but I continued to use constants out of habit. After all, both approaches seemed to produce the same results.

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

GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1                             5
a2                             3
           b1                  2
           b2                  3
           b3                  3
                               8
 

However, I later ran into a case where the two actually produced different results.

set null '(null)'

select
  grp_a
, grp_b
, nvl2( grp_b, 1, 0 ) nvl2_grp_b
, count(*)
from
  t
GROUP BY
  GROUPING SETS ( GRP_A, GRP_B, () )
order by
  grp_a, grp_b
;

GRP_A  GRP_B  NVL2_GRP_B   COUNT(*)
------ ------ ---------- ----------
a1     (null)          0          5
a2     (null)          0          3
(null) b1              1          2
(null) b2              1          3
(null) b3              1          3
(null) (null)          0          8

 

set null '(null)'

select
  grp_a
, grp_b
, nvl2( grp_b, 1, 0 ) nvl2_grp_b
, count(*)
from
  t
GROUP BY
  GROUPING SETS ( GRP_A, GRP_B, 0 )
order by
  grp_a, grp_b
;

GRP_A  GRP_B  NVL2_GRP_B   COUNT(*)
------ ------ ---------- ----------
a1     (null) (null)              5
a2     (null) (null)              3
(null) b1              1          2
(null) b2              1          3
(null) b3              1          3
(null) (null)          0          8
 

Note how Query 2 returns "(null)" in the NVL2_GRP_B column and Query 1 does not. This is because "0" appears in both the SELECT list and the GROUP BY clause. Readers who want to understand more about why these two queries differ can reverse engineer the two into their UNION ALL equivalents using the instructions at Reverse Engineering GROUPING BY Queries. Readers who don't simply need to remember this rule of thumb - always use an empty set term to generate a grand total row, do not use a constant.