1

I have two tables, one has products shipped in and other table where product is shipped out. I just need to have a total number of product still not shipped. It can be easily done by calculating the difference but I am facing some issues.

Stock In Flow Table:

Product ID Quantity In
1 15
1 5
2 5
2 10
3 15
4 5

Stock Out Flow Table:

Product ID Quantity Out
1 7
2 3
3 5
2 2
1 8
2 2
1 5
3 3

I am using this query

SELECT
         "Stock In Flow Table"."Product ID" 'Product ID',
         sum("Stock In Flow Table"."Quantity In") as "Quantity In",
         sum("Stock Out Flow Table"."Quantity Out") as "Quantity Out",
        "Quantity In" -"Quantity Out" as "InStock"
FROM  "Stock In Flow Table"
JOIN "Stock Out Flow Table" ON "Stock Out Flow Table"."Product ID"  = "Stock In Flow Table"."Product ID"  
GROUP BY  "Product ID", "InStock"

The desired result should be this

Product ID InStock
1 0
2 8
3 7
4 5

However The numbers are not correct. It is adding up all the numbers multiple times. I have tried multiple joins but still not getting the desired result. Please help me to check where I am going wrong?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Yunus Surty
  • 25
  • 3
  • Does your query REALLY have `"` around table and column names? – RiggsFolly Jun 14 '22 at 08:17
  • You need to aggregate each table separately and then subtract. – Stu Jun 14 '22 at 08:17
  • Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) – philipxy Jun 14 '22 at 08:51

1 Answers1

0

You need to aggregate each table first and using an outer query to do the calculations. The problem you might face is the use of LEFT JOIN which in your case will give null for id 4 because 5-null gives null. To solve that use a case condition , when tot_quantity_out is null then get tot_quantity_in value.

Try:

   select t1.product_id,
       case when tot_quantity_out is null then tot_quantity_in else tot_quantity_in - tot_quantity_out end as InStock
from ( select product_id,sum(quantity_in) as tot_quantity_in
       from stock_in 
       group by product_id 
     ) as t1 
left join 
     ( select product_id,sum(quantity_out) as tot_quantity_out
       from stock_out 
       group by product_id
     ) as t2 on t1.product_id=t2.product_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c0fba72a3a1b72d651d04bbb9447665c

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28