
-- demo grouping sets
-- grouping sets is used to create subtotals by owner-object, owner, object_type and all
-- the empty set () ensures total for all rows

col owner format a20
col object_count format a30
col obj_count format 999,999

break on owner skip 1 on report skip 1


with obj as(
	select
		owner
		, object_type
		, count(*) obj_count
		, grouping(owner) own_grp
		, grouping(object_type) obj_grp
	from dba_objects
	where owner in ('WMSYS','XDB')
	group by grouping sets ( 
		(owner,object_type) 
		, (owner)
		, (object_type)
		, ()  -- total for all rows
	)
)
select 
	decode(own_grp,1,'DB TOTAL',owner) owner
	, decode(obj_grp,1,'OWNER TOTAL',object_type) object_type
	, obj_count
	, own_grp, obj_grp
from obj
order by own_grp,owner,obj_grp,object_type
/

