The last type of grouping shortcut we will examine is called a Concatenated Grouping. With it one can re-write a query like this one, which effectively performs a cross-product of GRP_A with GRP_B and GRP_C,
select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets
(
( grp_a, grp_b )
, ( grp_a, grp_c )
)
order by
1, 2, 3
;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 b1 2 a1 b2 3 a1 c1 4 a1 c2 1 a2 b3 3 a2 c2 3
into one like this.
set null '(null)' select grp_a , grp_b , grp_c , count(*) from t group by grp_a , grouping sets( grp_b, grp_c ) order by 1, 2, 3 ;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 b1 (null) 2 a1 b2 (null) 3 a1 (null) c1 4 a1 (null) c2 1 a2 b3 (null) 3 a2 (null) c2 3
The cross-product effect is more apparent when a query like this one
select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets
(
( grp_a, grp_c )
, ( grp_a, grp_d )
, ( grp_b, grp_c )
, ( grp_b, grp_d )
)
order by
1, 2, 3
;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 (null) c1 4 a1 (null) c2 1 a1 (null) (null) 5 a2 (null) c2 3 a2 (null) (null) 3 (null) b1 c1 2 (null) b1 (null) 2 (null) b2 c1 2 (null) b2 c2 1 (null) b2 (null) 3 (null) b3 c2 3 (null) b3 (null) 3
is re-written into one like this.
select grp_a , grp_b , grp_c , count(*) from t group by grouping sets( grp_a, grp_b ) , grouping sets( grp_c, grp_d ) order by 1, 2, 3 ;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 (null) c1 4 a1 (null) c2 1 a1 (null) (null) 5 a2 (null) c2 3 a2 (null) (null) 3 (null) b1 c1 2 (null) b1 (null) 2 (null) b2 c1 2 (null) b2 c2 1 (null) b2 (null) 3 (null) b3 c2 3 (null) b3 (null) 3
Personally I have never found the need to use concatenated groupings. I find that specifically listing the desired groupings in a single GROUPING SETS clause or using a single ROLLUP or CUBE operator makes my queries easier to understand and debug. Concatenated groupings can, however, prove useful in data warehouse queries that deal with hierarchical cubes of data. See Concatenated Groupings for more information.