codeSeven codeSeven - 5 months ago 23
MySQL Question

Union all table for result

I just want to get result in my 3 table. First it will sum qty of each itemcode. Then output the result based on my formula below.

SUM(table 1)
itemcode qty date
001 20 06-17
002 20 06-17
001 10 06-18


+(add) of

SUM(table 2)
itemcode qty date
001 10 06-17
002 40 06-17
001 5 06-18

-(subtract) of

table 3
itemcode qty date
001 5 06-17
002 5 06-17
002 5 06-18


Result :

itemcode qty
001 40
002 50

Answer

use the below query

select t.itemcode as itemcode,sum(t.qty) as qty
from ( 
select itemcode,qty from table1
union all
select itemcode,qty from table2
union all
select itemcode,(qty * -1) from table3) as t
group by t.itemcode
Comments