SQL Snippets ™: Tutorials for Oracle Developers

There are times when the results of two or more different groupings are required from a single query. For example, say we wanted to combine the results of these two queries.

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

GRP_A        COUNT(*)
---------- ----------
a1                  5
a2                  3
 

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

GRP_B        COUNT(*)
---------- ----------
b1                  2
b2                  3
b3                  3
 

UNION ALL could be used, like this

select grp_a, null, count(*)
from   t
group by grp_a
UNION ALL
select null, grp_b, count(*)
from   t
group by grp_b
order by 1, 2 ;
 

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

but as of Oracle 9i a more compact syntax is available with the GROUPING SETS extension of the GROUP BY clause. With it the last query can be written as follows.

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
 

It is important to understand how the clause grouping sets( grp_a, grp_b ) used in the last query differs from the clause group by ( grp_a, grp_b ) in the next query.

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

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

Note how the last query returned different rows than the GROUPING SETS query did even though both used the term (GRP_A, GRP_B).