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:

Is there a simple in sql way to sum it up to only get:

1   60
2   80
3   80

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

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

Method 3

Subquery:

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

Method 4

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.

Method 5

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

Method 6

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

Method 7

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…

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating