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:

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

1   60
2   80
3   80

score 95 · Accepted Answer · answered Mar 05 '10 at 15:21

95

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

answered Mar 05 '10 at 15:21

Jimmy

89,068
17
119
137

1

I'm looking for a way to accomplish this in a view, but sub-queries aren't allowed in views. Is there an alternative? – Peter Kirby Jun 25 '14 at 20:53
2

make the subquery another view – Jimmy Jun 26 '14 at 03:03

score 16 · Answer 2 · answered Mar 05 '10 at 15:20

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.

score 4 · Answer 3 · answered Mar 05 '10 at 15:22

4

Subquery:

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

answered Mar 05 '10 at 15:22

adharris

3,591
1
21
18

Note: you shouldn't use select * in this case. – David Oneill Mar 05 '10 at 15:25
Note 2: I bet mysql will ask you to assign alias to subquery – zerkms Mar 05 '10 at 15:27
@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. – adharris Mar 05 '10 at 15:33
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... :) – David Oneill Mar 05 '10 at 15:55

score 1 · Answer 4 · answered Nov 06 '17 at 20:12

1

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.

answered Nov 06 '17 at 20:12

just_myles

235
2
9

As was mentioned. This requires a group by id. – just_myles May 06 '20 at 18:47

score 1 · Answer 5 · answered Apr 09 '22 at 05:47

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

score 0 · Answer 6 · edited Feb 10 '20 at 00:04

0

Yes!!! Its okay! Thanks!!!! My code finishing:

SELECT SUM(total) 
FROM ( 
        (SELECT 1 as id, SUM(e.valor) AS total  FROM entrada AS e)
    UNION 
        (SELECT 1 as id, SUM(d.valor) AS total FROM despesa AS d)
    UNION 
        (SELECT 1 as id, SUM(r.valor) AS total FROM recibo AS r WHERE r.status = 'Pago')
)  x group by id

edited Feb 10 '20 at 00:04

paul-shuvo

1,874
4
33
37

answered Feb 09 '20 at 21:05

Norberto Alcântara

1
2

Hamza Rahman · Answer 7 · 2022-02-08T05:24:14.767

SELECT      BANKEMPNAME,  workStation, SUM (CALCULATEDAMOUNT) FROM(
SELECT      BANKEMPNAME, workStation, SUM(CALCULATEDAMOUNT) AS CALCULATEDAMOUNT,SALARYMONTH
FROM        dbo.vw_salaryStatement
WHERE       (ITEMCODE  LIKE 'A%') 
GROUP BY    BANKEMPNAME,workStation, SALARYMONTH
union all
SELECT      BANKEMPNAME, workStation,  SUM(CALCULATEDAMOUNT) AS CALCULATEDAMOUNT,SALARYMONTH
FROM        dbo.vw_salaryStatement
WHERE       (ITEMCODE  NOT LIKE 'A%')
GROUP BY    BANKEMPNAME, workStation, SALARYMONTH) as t1
WHERE       SALARYMONTH BETWEEN '20220101' AND '20220131'
group by    BANKEMPNAME,  workStation
order by    BANKEMPNAME asc

IN MSSQL You can write this way, But Doing UNION ALL THE Column should be the same for both ways.

I have given this example So that you can understand the process...

@EricAya I have already changed the letter please check. Thank you — Hamza Rahman, Feb 08 '22 at 05:25

A simple way to sum a result from UNION in MySQL

7 Answers7

Linked