SQL Snippets ™: Tutorials for Oracle Developers

Composite Columns

You can treat a collection of columns as an individual set by using parentheses in the GROUPING SETS clause. For example, to write a query that returns the equivalent of these two queries

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
 

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

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

we could use the following GROUPING SETS clause.

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

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

The term (GRP_A, GRP_B) is called a "composite column" when it appears inside a GROUPING SETS, ROLLUP, or CUBE clause.

Empty Sets

To add a grand total row to the result set an empty set, specified as (), can be used. In the example below the last row is generated by the empty set grouping.

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         b1                  2
a1         b2                  3
a2         b3                  3
                               8
 

Gotcha - Parentheses without GROUPING SETS

Outside a GROUPING SETS clause (or ROLLUP or CUBE clauses) a parenthesized expression like (GRP_A, GRP_B) is no different than the same expression without parentheses. For example this query

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

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

returns the same results as this query

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

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

which in turn has the same result set as this one.

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