Select Product_ID And Sum(Qty) From Another Table

Question

I have 2 tables, one is a recipe_tbl, and second is prepared_tbl as follow:

recipe_tbl

Product_ID	Product_Name	Req_Qty	Units
F-0001	Flour	120	gr
S-0001	Sugar	100	gr

prepared_tbl

Trans_Code	Trans_Date	Product_ID	Qty	Units
t-2302-001	2023-02-16	F-0001	10	gr
t-2302-002	2023-02-16	F-0001	10	gr
t-2302-003	2023-02-16	S-0001	10	gr

What I want is to generate sum as follow:

Product_ID	Product_Name	Req_Qty	Units	Prepared	Remaining
F-0001	Flour	120	gr	20	100
S-0001	Sugar	100	gr	10	90

How to do that in query?

This is my query, but it doesn't work :

select a.Product_ID , sum(b.Qty) 
from recipe_tbl a  
  left join prepared_tbl b 
on b.Product_ID = a.Product_ID 
group by a.Product_ID 

Please help we with the correct query, thank you in advance!

select a.Product_ID , sum(b.Qty) 
from recipe_tbl a  
  left join prepared_tbl b 
on b.Product_ID = a.Product_ID 
group by a.Product_ID 

Answer 1

You can use the following SQL query to generate the results you're looking for:

SELECT 
    r.Product_ID,
    r.Product_Name,
    r.Req_Qty,
    r.Units,
    SUM(p.Qty) AS Prepared,
    r.Req_Qty - SUM(p.Qty) AS Remaining
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;

To be in the safe side, when there are no matching rows in the 'prepared_tbl' use COALESCE to return 0. Check below query with COALESCE function:

SELECT 
    r.Product_ID,
    r.Product_Name,
    r.Req_Qty,
    r.Units,
    COALESCE(SUM(p.Qty), 0) AS Prepared,
    r.Req_Qty - COALESCE(SUM(p.Qty), 0) AS Remaining
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;