How do I aggregate using two tables in SQL?-CodePudding

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?

CodePudding user response：

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