SQL SUM ON JOIN

sir, I have 3 SQL tables viz stock, purchase, and sold. in the stock table, I have fields like SKU, qty, MRP, etc in the purchase table, I have fields like SKU, purch_qty, purch_date etc. and in the sold table, I have fields like SKU, sold_qty, sold_date etc.

my query is when I want to create a view where I can get to see all SKU with initial qty, sold_qty, puch_qty, availabe_Qty like (SKU, initial_qty, purchased_qty, sold_qty, stock_availabe)

I tried to join these tables on SKU but the result is coming double triple times of quantity.

could you please help me how can I get to this.

my sql query code is

    SELECT        stk.id, stk.sku, SUM(stk.qty) AS Initial_Qty, SUM(pur.qty) AS Purch_Qty, SUM(sld.qty) AS Sold_Qty
FROM            dbo.stock AS stk LEFT OUTER JOIN
                         dbo.sold AS sld ON stk.sku = sld.sku LEFT OUTER JOIN
                         dbo.purchase AS pur ON stk.sku = pur.sku
GROUP BY stk.id, stk.sku

Solution

Basically, you need to aggregate in subqueries before joining. The logic you want should look like:

select st.sku, st.qty stock_qty, pu.purch_qty, so.sold_qty
from stock st
left join (select sku, sum(purch_qty) purch_qty from purchase group by sku) pu 
    on pu.sku = st.sku
left join (select sku, sum(sold_qty) sold_qty from sold group by sku) so
    on so.sku = st.sku