CODEWITHSUNDEEP
sqloracle-databasesumaggregate-functions
SASPYTHON
SASPYTHON

Reputation: 1621

How to add/combine SUM from 2 tables Oracle, Basic

I am very new to SQL,

I have 2 code like this

Select ID, SUM(Column1) AS Sum1
from table1

Select ID, SUM(Column1) AS Sum2
from table2

AS result of table1

ID------ Sum1  
001      20
003      10
004      5

AS result of table2

ID------ Sum2  
001      20
003      10
005      10

I would like show the result after joining this 2 tables like this, how can I wirte the code to show like below?

    ID------ Sum 
    001      40
    003      20
    004      5
    005      10

Thank you so much

Upvotes: 0

Views: 56

Answers (2)

mcha
mcha

Reputation: 2998

SELECT ID, SUM(COLUMN1)
FROM (
    SELECT ID, COLUMN1 FROM T1
    UNION ALL
    SELECT ID, COLUMN1 FROM T2
)
GROUP BY ID;

note here that UNION ALL is used instead of UNION so you can show duplicated results

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

You can FULL JOIN the two subqueries together (after adding a GROUP BY to each), and use COALESCE() to return the proper value:

SELECT COALESCE(a.ID,b.ID) AS ID
     , COALESCE(a.Sum1,0)+COALESCE(b.Sum2,0) AS Sum
FROM (Select ID, SUM(Column1) AS Sum1
      from table1
      GROUP BY ID
     ) a
FULL JOIN (Select ID, SUM(Column1) AS Sum2
           from table2
           GROUP BY ID
           )b
  ON a.ID = b.ID

FULL JOIN or FULL OUTER JOIN will return records from each side of the join whether they join or not. COALESCE() returns the first non-NULL value from the list of parameters you supply it.

Alternatively, you could UNION the two sets prior to aggregating:

SELECT ID, SUM(Column1) AS Sum
FROM (Select ID, Column1
      FROM table1
      UNION ALL 
      Select ID, Column1
      FROM table2
      ) sub
GROUP BY ID

Upvotes: 0

Related Questions