Rollup in Oracle, Oracle

Language: Layout:

with t1 as (
select 'A' as cat1, 'a' as cat2, 'aa' as cat3 , 1 as f_id from dual union all
select 'A' as cat1, 'a' as cat2, 'ab' as cat3 , 2 as f_id from dual union all
select 'A' as cat1, 'b' as cat2, 'ba' as cat3 , 3 as f_id from dual union all
select 'A' as cat1, 'b' as cat2, 'bb' as cat3 , 4 as f_id from dual union all
select 'B' as cat1, 'c' as cat2, 'ca' as cat3 , 5 as f_id from dual union all
select 'B' as cat1, 'c' as cat2, 'cb' as cat3 , 6 as f_id from dual union all
select 'B' as cat1, 'd' as cat2, 'da' as cat3 , 7 as f_id from dual),
t2 as (
select 1 as f_id,   4 as val from dual union all
select 2 as f_id,   6 as val from dual union all
select 3 as f_id,   4 as val from dual union all
select 4 as f_id,   1 as val from dual union all
select 5 as f_id,   6 as val from dual union all
select 6 as f_id,   6 as val from dual union all
select 7 as f_id,   9 as val from dual )
select cat1, nvl(cat2,'_') as cat2, nvl(cat3, '_') as cat3, sum(val)
  from t1 join t2 on t1.f_id = t2.f_id
group by cat1, rollup (cat2, cat3)
order by cat1, cat2 nulls first, cat3 nulls first


absolute service time: 0,88 sec