a simple way to sum a result from UNION in MySql

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

SELECT id, SUM(amount) FROM
(
    SELECT id, SUM(amount) AS `amount` FROM t1 GROUP BY id
  UNION ALL
    SELECT id, SUM(amount) AS `amount` FROM t2 GROUP BY id
) `x`
GROUP BY `id`

I groupped each table and unioned because i think it might be faster, but you should try both solutions.


Subquery:

SELECT id, SUM(amount)
FROM ( SELECT * FROM t1
       UNION ALL SELECT * FROM t2
       UNION ALL SELECT * FROM t3
     )
GROUP BY id

Tags:

Mysql

Sql

Union