Geek asks and answers

How to combine two tables into their own 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 the same two columns: id, amount. How can I do this using SQL? For instance:

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)

therefore C should be:

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

Thank!

+5
source share
2 answers
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 database of tables as a result of 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;
+7
source

The answer above works perfectly fine. Just add the order by clause to it, which will sort by ID.

SELECT  ID, SUM(Amount) as total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP by ID
order by ID
0
source

More articles:


All Articles