'Use Join , Sum and Group by in a query with three table

I have three tables:

                                    **OBTable**
               Product           obquantity       obrate        obTotalAmmount
               Matadoor Pen      100              8             800
               Matadoor Pen      1000             4             4000

                                 **PurchaseTable**
               pProduct        Pquantity    pRate   SaleRate     pTotalAmmount
               Matadoor Pen    150          4       5            600
               Matadoor Pen    400          8       10           3200
               Matadoor Pen    1500         9       10           13500

                                   **SaleTable**
               sProduct             sQuantity    sRate        sTotalAmmount
               Matadoor Pen         100          10           1000
               Matadoor Pen         350          10           3500
               Matadoor Pen         1350         10           13500

My query:

SELECT Product, SUM(obQuantity) AS obQuantity, SUM(obTotalAmmount)/SUM(obQuantity) AS obRate, SUM(obTotalAmmount) AS obTotalAmmount,

pProduct, SUM(pQuantity) AS pQuantity, SUM(pTotalAmmount)/SUM(pQuantity) AS pRate, SUM(pTotalAmmount) AS pTotalAmmount,

sProduct, SUM(sQuantity) AS sQuantity, SUM(sTotalAmmount)/SUM(sQuantity) AS sRate, SUM(sTotalAmmount) as sTotalAmmount,

Sum(obQuantity) +SUM(pquantity) -Sum(squantity) as obpsQTY,

(Sum(obTotalAmmount)+Sum(pTotalAmmount)-sum(sTotalAmmount))/(Sum(obQuantity)+SUM(pQuantity)-Sum(sQuantity)) as obpsrate ,

(Sum(obTotalAmmount)+Sum(pTotalAmmount))-Sum(sTotalAmmount) as obpstotal

from OBTable

left join PurchaseTable on OBTable.Product=PurchaseTable.pProduct left join Saletable on PurchaseTable.pProduct=SaleTable.sProduct

Group BY OBTable.Product,PurchaseTable.pProduct,Saletable.sProduct

It's executed answer is not correct. Plz help me and give tips to solve it.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source