lkkeepmoving lkkeepmoving - 2 months ago 6
SQL Question

How to combine two tables into own this the same columns?

I have two tables A and B. A has two columns: id, amount. B also has two columns: id, amount.
I hope to combine A and B to create a new table C, with same two columns:id, amount. How can I do it using SQL?
For example:

A
('A1',1)
('A2',5)
('A3',2)
('A4',5)
('A5',2)
('A6',7)
B
('A1',3)
('A3',2)
('A4',7)
('A5',4)
('A8',2)
('A9',10)


so C should be:

C
('A1',4)
('A2',5)
('A3',4)
('A4',12)
('A5',6)
('A6',7)
('A8',2)
('A9',10)


Thank you!

Answer
SELECT  ID, SUM(Amount) total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP   BY ID

You can create a table base on the result from the query.

CREATE TABLE C
AS
SELECT  ID, SUM(Amount) total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP   BY ID;
Comments