MYSQL - SUM VALUE TWO TABLE

Question

I need sum quantity in a different field with alias.

Image

possible duplicate of [MySQL: sum() and join from multiple tables](http://stackoverflow.com/questions/10923905/mysql-sum-and-join-from-multiple-tables) — Funk Forty Niner, Apr 07 '16 at 15:23

gr1zzly be4r · Accepted Answer · 2016-04-07T16:10:41.393

0

I don't what your two table names are but this should work. You're trying to aggregate on idMaterial after joining on idMaterial.

SELECT A.idMaterial, C.nombre_material, A.sum_qty_entrada, B.sum_qty_salida, (A.sum_qty_entrada - B.sum_qty_salida) total 
FROM (
    SELECT idMaterial, SUM(qty) sum_qty_entrada FROM mesa_entrada
    GROUP BY idMaterial
)
    AS A 
JOIN (
    SELECT idMaterial, SUM(qty) sum_qty_salida FROM mesa_salida
    GROUP BY idMaterial
) AS B ON 
    A.idMaterial = B.idMaterial
JOIN mesa_con_nombre_material AS C ON
    A.idMaterial = C.idMaterial

edited Apr 07 '16 at 16:10

answered Apr 07 '16 at 15:25

gr1zzly be4r

2,072
1
18
33

different result friend :( – Tony Muñoz Apr 07 '16 at 15:29
What was the result? – gr1zzly be4r Apr 07 '16 at 15:42
I edited my answer to aggregate before the join. My initial answer probably had inflated sums. – gr1zzly be4r Apr 07 '16 at 15:46
Its Correct Friend, But i need idMaterial 4 with total 0 – Tony Muñoz Apr 07 '16 at 15:50
i have more material but with 0 transaction – Tony Muñoz Apr 07 '16 at 15:50
There isn't an `idMaterial` 4 in the input tables so it won't show up in the join. – gr1zzly be4r Apr 07 '16 at 16:03
OHH is correct , but I need the name of material, idMaterial is another table – Tony Muñoz Apr 07 '16 at 16:09
Just join in the table that has the name of the material on the id of the material. I edited my answer. – gr1zzly be4r Apr 07 '16 at 16:11
Please accept the answer and open up another question if you're also looking to find the date. I'll answer the question there. – gr1zzly be4r Apr 07 '16 at 16:58

score 0 · Answer 2 · answered Apr 07 '16 at 15:26

0

i think that you need this

SELECT SUM(hr) FROM
    (
      Select sum(qty) as hr FROM table1 
      UNION ALL
      Select sum(qty) as hr FROM table2 
    )a

answered Apr 07 '16 at 15:26

Ezequiel García

2,616
19
12

you can help me?, i can share my database – Tony Muñoz Apr 07 '16 at 15:32
of course i can help you but you test this query what you need exactly? – Ezequiel García Apr 07 '16 at 15:36

MYSQL - SUM VALUE TWO TABLE

2 Answers2