SQL / итоги, подитоги / rollup, cube

Группировка и итоги

1 способ (union all)

select object_type, count(*)
from user_objects
group by object_type
union all
select 'TOTAL', count(*)
from user_objects

2 способ (rollup)

select decode(grouping(object_type), 0, object_type, 'TOTAL') as object_type, count(*)
from user_objects t
group by rollup(object_type)

total_total

Итоги, подитоги rollup

select object_type, owner,
case grouping(object_type)||grouping(owner)
when '00' then 'TOTAL BY OBJECT_TYPE AND OWNER'
when '10' then 'TOTAL BY OBJECT_TYPE'
when '01' then 'TOTAL BY OWNER'
when '11' then 'GRAND TOTAL'
end TOTAL,
count(*)
from ALL_OBJECTS t
where t.object_type='JOB'
group by rollup(object_type, owner)

total_rollup

Итоги, подитоги cube

select object_type, owner,
case grouping(object_type)||grouping(owner)
when '00' then 'TOTAL BY OBJECT_TYPE AND OWNER'
when '10' then 'TOTAL BY OBJECT_TYPE'
when '01' then 'TOTAL BY OWNER'
when '11' then 'GRAND TOTAL'
end TOTAL,
count(*)
from ALL_OBJECTS t
where t.object_type='JOB'
group by cube(object_type, owner)
order by grouping(owner), grouping(object_type)

total_cube