SQL query with UNION refinement

Question

I would like to know if there is a better way to code the below requirement: I am using Oracle 11.x db

Table setup:

create table S_ASSET (ROW_ID varchar2(10), X_QUOTE_ID varchar2(10), SP_NUM varchar2(10), AMT number(10), ASSET_NUM varchar2(10));
create table S_QUOTE_ITEM (ROW_ID varchar2(10), AMT number(10));
create table S_QUOTE_ITEM_XM (PAR_ROW_ID varchar2(10), AMT number(10));

Table 1-

insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('1', 'A1', '000', 10, 'AAA');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('1', 'A2', '000', 20, 'AAA');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('2', 'B1', '111', '', 'BBB');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('3', 'C1', '222', 10, 'CCC');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('3', 'C2', '222', 0, 'CCC');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('4', 'D1', '333', 10, 'DDD');
insert into S_ASSET (ROW_ID, X_QUOTE_ID, SP_NUM, AMT, ASSET_NUM) values ('5', 'E1', '444', 0, 'EEE');

Table 2-

insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('A1', 5);
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('A2', '');
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('B1', 0);
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('C1', 5);
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('C2', 0);
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('D1', '');
insert into S_QUOTE_ITEM (ROW_ID, AMT) values ('E1', 5);

Table 3-

insert into S_QUOTE_ITEM_XM (PAR_ROW_ID, AMT) values ('A1', 1);
insert into S_QUOTE_ITEM_XM (PAR_ROW_ID, AMT) values ('A1', '');
insert into S_QUOTE_ITEM_XM (PAR_ROW_ID, AMT) values ('B1', 1);

where x-quote_id (of Table 1)=row_id (of Table 2)=par_row_id (of Table 3)

I need the sum of AMT columns grouped by sp_num. Below is the query which I have written. I am looking for a shorter more efficient way to write the same.

select sp_num, sum(amt) as DocFee
from
(   
    (
        select x_quote_id row_id, amt, sp_num
        from s_asset
    )
    union
    (   
        select a.row_id, a.amt, b.sp_num
        from
        (   
            select row_id, sum(amt) amt
            from s_quote_item
            group by row_id
        ) a,
        s_asset b
        where b.x_quote_id = a.row_id
    )
    union      
    (
        select a.par_row_id row_id, a.amt, b.sp_num
        from
        (
            select par_row_id, sum(amt) amt
            from s_quote_item_xm
            group by par_row_id
        ) a,
        s_asset b
        where b.x_quote_id = a.par_row_id
    )
)
group by sp_num 
order by sp_num

Expected O/P:

sp_num    DocFee
000       36
111       1
222       15
333       10
444       5

Thanks to all who came up with their efficient solutions. I am however struggling to combine below queries (owing to the fact that I am completely new SQL), all of which are having a common factor sp_num

Query 1: (Parent)

select  POL.SP_NUM POL#
,       POL.ASSET_NUM COV#
,       sum(POL.AMT) SI
from S_ASSET POL
group by
        POL.ASSET_NUM
,        POL.SP_NUM
order by POL.sp_num

Query 2: (GP)

select 
  sp_num as pol#,
  coalesce(sum(
    (
      select sum(deb.amt)
      from s_invoice deb
      where deb.fn_accnt_id = pol.row_id
      and deb.debit_type = 'Customer' 
    )
  ), 0) -
  coalesce(sum(
    (
      select sum(cred.amt) 
      from s_src_payment cred
       where cred.asset_id = pol.row_id
      and cred.cg_dedn_type_cd = 'Customer' 
    )), 0)
   as gp
from s_asset pol 
group by sp_num
order by sp_num

Query 3: (DOCFEE) (either the below or the result shared by Juan Carlos in this thread)

select sp_num, sum(amt) as docfee
from ((select a.sp_num, a.amt as amt
       from s_asset a
      ) union all
      (select a.sp_num, q.amt as amt
       from s_asset a join
        s_quote_item q
            on q.row_id = a.x_quote_id
      ) union all
      (select a.sp_num, x.amt as amt
       from s_asset a join
            s_quote_item_xm x
            on x.par_row_id = a.x_quote_id
      )
     ) aqx
group by sp_num
order by sp_num;

Answer 1

SQL Fiddle Demo

WITH A as (
     SELECT sp_num, X_QUOTE_ID, sum(AMT) AMT
     FROM S_ASSET
     GROUP BY sp_num, X_QUOTE_ID
), B as (
     SELECT ROW_ID, sum(AMT) AMT
     FROM S_QUOTE_ITEM
     GROUP BY ROW_ID
), C as (
     SELECT PAR_ROW_ID, sum(AMT) AMT
     FROM S_QUOTE_ITEM_XM
     GROUP BY PAR_ROW_ID  
)
SELECT sp_num, COALESCE(SUM(A.AMT),0) + 
               COALESCE(SUM(B.AMT),0) + 
               COALESCE(SUM(C.AMT),0) DOCFEE
FROM A
LEFT JOIN B
       ON A.X_QUOTE_ID = B.ROW_ID
LEFT JOIN C
       ON A.X_QUOTE_ID = C.PAR_ROW_ID
GROUP BY sp_num     
ORDER BY sp_num;

Answer 2

I think it is best to aggregate each of the subtables at thelevel of sp_num and then do a final aggregation:

select sp_num, sum(amt)
from ((select a.sp_num, sum(a.amt) as amt
       from s_asset a
       group by a.sp_num
      ) union all
      (select a.sp_num, sum(q.amt) as amt
       from s_asset a join
            s_quote_item q
            on q.row_id = a.x_quote_id
       group by a.sp_num
      ) union all
      (select a.sp_num, sum(x.amt) as amt
       from s_asset a join
            s_quote_item_xm x
            on x.par_row_id = a.x_quote_id
       group by a.sp_num
      )
     ) aqx
group by sp_num;

If you like, you can shorted the query by removing the first level of aggregations:

select sp_num, sum(amt)
from ((select a.sp_num, a.amt as amt
       from s_asset a
      ) union all
      (select a.sp_num, q.amt as amt
       from s_asset a join
            s_quote_item q
            on q.row_id = a.x_quote_id
      ) union all
      (select a.sp_num, x.amt as amt
       from s_asset a join
            s_quote_item_xm x
            on x.par_row_id = a.x_quote_id
      )
     ) aqx
group by sp_num;

My guess is that Oracle will produce a better execution plan for the first version, under most circumstances.