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

Question

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

your first queries would return an error, I assume a group by clause is missing in the example, what have you tried so far? — mcha
– mcha, Commented Jul 8, 2016 at 16:46

peterpepo · Accepted Answer · 2016-07-08 17:01:55Z

2

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

edited Jul 8, 2016 at 17:01

peterpepo

785 bronze badges

answered Jul 8, 2016 at 16:53

mcha

3,0085 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hart CO · Accepted Answer · 2016-07-08 16:52:48Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related