
-- use grouping_id() to control which group by row are included


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


with obj as(
  select
	decode(grouping(OWNER),1,'TOTAL_OBJECTS',owner) owner
	,decode(grouping(object_type),1,'OBJECT_COUNT',object_type) object_type
	,count(*) obj_count
	,grouping(object_type) obj_gid
	,grouping(owner) own_gid
	,grouping_id(owner,object_type) own_grpid
	,grouping_id(object_type, owner) obj_grpid
	from dba_objects
	where owner in ('WMSYS','XDB')
	group by cube(owner, object_type)
)
select owner, object_type, obj_count
	, own_grpid
	, obj_grpid
from obj
-- show owners and sub totals
--where (own_grpid, obj_grpid) in ( (0,0) , (1,2))
-- show totals only
where (own_grpid, obj_grpid) in ( (2,1) , (3,3))
order by own_gid, owner, obj_gid,object_type
/
