It often happens that a query will have a group A which is a superset of group B which in turn is a superset of group C. When aggregates are required at each level a query like this can be used.
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 )
, ()
)
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) (null) 5 a2 b3 c2 3 a2 b3 (null) 3 a2 (null) (null) 3 (null) (null) (null) 8
This arrangement is common enough that SQL actually provides a shortcut for specifying these types of GROUPING SETS clauses. It uses the ROLLUP operator. Here is how the query above looks when implemented with ROLLUP.
select grp_a , grp_b , grp_c , count(*) from t group by ROLLUP( 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) (null) 5 a2 b3 c2 3 a2 b3 (null) 3 a2 (null) (null) 3 (null) (null) (null) 8