Join two query's

user_15638849 0
Viewed 36

I have this two querys, I'm trying to make a left join to get some data, but i don't success

the first query i get is

SELECT parte, sum(cantidad) FROM `directoscount` where deleted_at is null group by parte

this is the result that i have with that query and is correct

enter image description here

and the second is

select material, sum(totalStock) from lx02 group by material

and this is the second result

enter image description here

I want to make a left join and i try with something like this

SELECT directoscount.parte, SUM(directoscount.cantidad), lx02.material, SUM(lx02.totalStock) FROM directoscount LEFT JOIN lx02 ON directoscount.parte = lx02.material GROUP BY directoscount.parte, lx02.material

and the result of my query bring data to much different

enter image description here

mysql
1 Answers

You second query doesn't have where deleted_at is null which make the comparison of SUM(directoscount.cantidad) unequal.

SUM(lx02.totalStock) is different in the final query because it is being counted for each directoscount.parte. So for parte 486298 the last query shows a total of 2076. divide this by 346 and you get exactly 6, the number of parte=476298 entries.

SELECT directoscount.parte,
       SUM(directoscount.cantidad),
       lx02.material,
       SUM(lx02.totalStock)/COUNT(directoscount.parte)
FROM directoscount
LEFT JOIN lx02
  ON directoscount.parte = lx02.material
GROUP BY directoscount.parte, lx02.material
user_101951530
Related