sql - Reuse subquery without using temp table or WITH clause in Oracle database -

in oracle 11g, have table, named item, in each record categorize maingroup , subgroup follows:

+-------+---------+----------+  item_id maingroup subgroup +-------+---------+---------+        1 group1    subgroup1        2 group1    subgroup2        3 group2    subgroup1        4 group2    subgroup2  ... 

i have write program report number of items in table item. report output similar this:

                 subgroup1   subgroup2   group_total group1                  10           5            15 group2                   0           1             1 subgroup_total          10           6            16 

to that, write sql query data , reformat output using java. sql should make complete report itself, i.e. use java reformat output , not calculation. so, decided output of sql should this:

+--------------+-----------+-----+  maingroup      subgroup    cnt +--------------+-----------+-----+   group1         subgroup1      10  group1         subgroup2       5  group1         group_total    15  group2         subgroup1       0  group2         subgroup2       1  group2         group_total     1  subgroup_total subgroup1      10  subgroup_total subgroup2       6  subgroup_total group_total    16 

ideally, sql simple as

select maingroup, subgroup, count(*) cnt item  group maingroup, subgroup union select maingroup, 'group_total' subgroup, count(*) cnt item group maingroup union select 'subgroup_total' maingroup, subgroup, count(*) cnt item group subgroup; 

but no, simplified table item make question easy understand, actually, make item, have use big subquery, it's looks like

select maingroup, subgroup, count(*) cnt         (select maingroup, subgroup ...) item  group maingroup, subgroup union select maingroup, 'group_total' subgroup, count(*) cnt         (select maingroup, subgroup ...) item  group maingroup union select 'subgroup_total' maingroup, subgroup, count(*) cnt        (select maingroup, subgroup ...) item  group subgroup; 

which use same big subquery 3 times. wish can reuse subquery don't have permission create tables or use clause. is possible rearrange above sql such subquery item process once without creating temporary table or using clause?

try query grouping sets clause:

select coalesce( maingroup, 'subgroup_total' ) maingroup,        coalesce( subgroup, 'group_total' ) subgroup,        count(*) cnt  very_complex_subquery group grouping sets((maingroup, subgroup), (maingroup), (subgroup)) order 1,2 ; 

demo: http://sqlfiddle.com/#!4/8ed9b/4