Search code examples
sqlmysqlunion

A simple way to sum a result from UNION in MySQL


I have a union of three tables (t1, t2, t3).
Each rerun exactly the same number of records, first column is id, second amount:

1  10
2  20
3  20

1  30
2  30
3  10

1  20
2  40
3  50

Is there a simple way in SQL to sum it up, i.e. to only get:

1   60
2   80
3   80

Solution

  • select id, sum(amount) from (
        select id,amount from table_1 union all
        select id,amount from table_2 union all
        select id,amount from table_3
    ) x group by id