sql

How to group from multiple queries in SQL


I have a simple SQL script that contain two tables, all of them have two columns ID and value, and I am joining them using union, but the issue is if two table have the same id the id will be duplicated but I want the id to be shown one time and sum the value so instead of this:

id value
1 6
1 5
2 7

I want this:

id value
1 11
2 7

here is my script:

select v.id,  v.value 
from (
SELECT  h.slxact# as id, (sum(nvl(d.DSCNT_AMT,0))  +     sum(nvl(d.PYMT_AMT,0))) as value  
FROM  pymnt d, invoice h
WHERE  d.slxact# = h.slxact#
and d.branch_cd = h.branch_cd
group by h.slxact#

UNION 

SELECT  h.slxact# as id,   sum(nvl(d.PYMT_AMT,0))  as value   
FROM   retn d, invoice h
WHERE  d.slxact# = h.slxact#
and d.branch_cd = h.branch_cd
group by h.slxact#
) as v

group by v.id

so my question is how I sum the value of similar ids, any help will be appreciated.


Solution

  • You could do this:

    select 
    v.id
    ,sum(v.value)
    
    from ( select id, 
           value1 as value
           from table_1
           union
           select id, 
           value2 as value
           from table_2
    ) v
    group by v.id;