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

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

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.

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

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

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...