Yahia Baiba Yahia Baiba - 3 months ago 14
SQL Question

SQL Join 3 tables group sum


  • Products with Id-Prod , desc, date fields

  • ENTRIES with Id-Prod Quantity, Amount.

  • Sorties with Id-Prod Quantity, Amount.



I want to make a query which join the 3 tables on Id-Prod and grouping it and sum the quantity and Amount for each products them calculate the stock which is equal to sum quantityIn -Sum QUantityOut
Thank you

Answer

To join and sum with 1-N looks like this -- we need to do a sub query to get the sums before the left join and a coalesce to set nulls to 0.

SELECT P.ID-Prod,
       COALESCE(E.s,0) AS IN-Q,
       COALESCE(S.s,0) AS OUT-Q, 
       COALESCE(E.s,0) - COALESCE(S.s,0) AS Stock
FROM Products P
LEFT JOIN (SELECT ID-Prod, SUM(Quantity) AS s 
           FROM ENTRIES GROUP BY ID-Prod) E ON P.ID-Prod = E.ID-Prod
LEFT JOIN (SELECT ID-Prod, SUM(Quantity) AS s
           FROM Sorties GROUP BY ID-Prod) S ON P.ID-Prod = S.ID-Prod
GROUP BY P.ID-Prod
Comments