a simple way to sum a result from UNION in MySql

Question

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 in sql way to sum it up to only get:

1   60
2   80
3   80

Solution 1

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

Solution 2

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.

Solution 3

Subquery:

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

Solution 4

As it's not very clear from previous answers, remember to give aliases (on MySQL/MariaDb) or you'll get error:

Every derived table must have its own alias

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
) AS 'aliasWhichIsNeeded'
 group by id

Solution 5

Not sure if MySQL uses common table expression but I would do this in postgres:

WITH total AS(
              SELECT id,amount AS amount FROM table_1 UNION ALL
              SELECT id,amount AS amount FROM table_2 UNION ALL
              SELECT id,amount AS amount FROM table_3
             )
SELECT id, sum(amount)
  FROM total

I think that should do the trick as well.

View more solutions

Comments

[David Oneill]

Note: you shouldn't use select * in this case.

[zerkms]

Note 2: I bet mysql will ask you to assign alias to subquery

[adharris]

@David: For sure, my bad. Probably doesn't matter if id and amount are the only two columns, but what are the chances of that? @zerkms: Also true.

[David Oneill]

exactly. And even if there are only id and amount for now, there's no sense in shooting yourself in the foot when you add a third column in 6 months... :)

[Peter Kirby]

I'm looking for a way to accomplish this in a view, but sub-queries aren't allowed in views. Is there an alternative?

[Boti]

make the subquery another view

[just_myles]

As was mentioned. This requires a group by id.

[Hamza Rahman]

@EricAya I have already changed the letter please check. Thank you