There are times when all combinations of a collection of grouping columns are required, as in this query.
set null '(null)'
select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets
(
( grp_a, grp_b, grp_c )
, ( grp_a, grp_b )
, ( grp_a, grp_c )
, ( grp_b, grp_c )
, ( grp_a )
, ( grp_b )
, ( grp_c )
, ()
)
order by
1, 2, 3
;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 b1 c1 2 a1 b1 (null) 2 a1 b2 c1 2 a1 b2 c2 1 a1 b2 (null) 3 a1 (null) c1 4 a1 (null) c2 1 a1 (null) (null) 5 a2 b3 c2 3 a2 b3 (null) 3 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 (null) (null) c1 4 (null) (null) c2 4 (null) (null) (null) 8
This arrangement is common enough that SQL provides a shortcut called the CUBE operator to implement it. Here is how the query above looks after re-writing it to use CUBE.
select grp_a , grp_b , grp_c , count(*) from t group by CUBE( GRP_A, GRP_B, GRP_C ) order by 1, 2, 3 ;
GRP_A GRP_B GRP_C COUNT(*) ---------- ---------- ---------- ---------- a1 b1 c1 2 a1 b1 (null) 2 a1 b2 c1 2 a1 b2 c2 1 a1 b2 (null) 3 a1 (null) c1 4 a1 (null) c2 1 a1 (null) (null) 5 a2 b3 c2 3 a2 b3 (null) 3 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 (null) (null) c1 4 (null) (null) c2 4 (null) (null) (null) 8