CODEWITHSUNDEEP
sqloracle-database
asinkxcoswt
asinkxcoswt

Reputation: 2544

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

In Oracle 11g, I have a table, named ITEM, in which each record was categorize into maingroup and subgroup as follows:

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

I have to write a program to report number of items in table ITEM. The report output will be similar to this:

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

To do that, I will write an SQL to query the data and then reformat the output using Java. The SQL should make the complete report itself, i.e. I will use Java only to reformat the output and not to do any calculation. So, I decided that the output of the SQL should be like 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, the SQL would be as simple as

select maingroup, subgroup, count(*) cnt from ITEM 
group by maingroup, subgroup union all
select maingroup, 'group_total' as subgroup, count(*) cnt from ITEM
group by maingroup union all
select 'subgroup_total' as maingroup, subgroup, count(*) cnt from ITEM
group by subgroup;

But no, I simplified the table ITEM the make the question easy to understand, actually, to make up this ITEM, I have to use a big subquery, so it's actually looks like

select maingroup, subgroup, count(*) cnt from 
       (select maingroup, subgroup from ...) ITEM 
group by maingroup, subgroup union all
select maingroup, 'group_total' as subgroup, count(*) cnt from 
       (select maingroup, subgroup from ...) ITEM 
group by maingroup union all
select 'subgroup_total' as maingroup, subgroup, count(*) cnt from
       (select maingroup, subgroup from ...) ITEM 
group by subgroup;

which use the same big subquery 3 times. I wish I can reuse this subquery but I don't have permission to create tables or to use the WITH clause. Is it possible to rearrange the above SQL such that the subquery ITEM is process only once without creating temporary table or using the WITH clause?

Upvotes: 1

Views: 763

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try a query with grouping sets clause:

SELECT coalesce( maingroup, 'subgroup_total' ) As maingroup,
       coalesce( subgroup, 'group_total' ) As subgroup,
       count(*) cnt 
FROM very_complex_subquery
GROUP BY GROUPING SETS((maingroup, subgroup), (maingroup), (subgroup))
order by 1,2
;

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

Upvotes: 5

Related Questions