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

Question

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.

Answer 1

You can try this code:

Select Product,
Sum(obQuantity) As obQuantity,
(SUM(obTotalAmmount)/Sum(obQuantity)) As obRate,
SUM(obTotalAmmount) as obTotalAmmount,
pQuantity,pRate,pTotalAmmount,
sQuantity ,sRate,sTotalAmmount,
(Sum(obQuantity) +pquantity) -squantity as obpsQTY,
(((SUM(obTotalAmmount)/Sum(obQuantity)))+pRate)/2 as  obpsrate ,
((((SUM(obTotalAmmount)/Sum(obQuantity)))+pRate)/2)*((Sum(obQuantity) +pquantity) -squantity) as obpstotal 
from OBTable 
left join (
Select pProduct,Sum(pQuantity) As pQuantity ,(Sum(pTotalAmmount)/Sum(pQuantity)) As pRate,Sum(pTotalAmmount) As pTotalAmmount 
from PurchaseTable 
Group by PurchaseTable.pProduct
)
PurchaseTable on OBTable.Product=PurchaseTable.pProduct
left join
(
Select sProduct,Sum(sQuantity) As sQuantity ,(Sum(sTotalAmmount)/Sum(sQuantity)) As sRate,Sum(sTotalAmmount) As sTotalAmmount 
from SaleTable 
Group by SaleTable.sProduct 
) Saletable on PurchaseTable.pProduct=SaleTable.sProduct 
Group by OBTable.Product,PurchaseTable.pProduct,Saletable.sProduct,PurchaseTable.pQuantity,PurchaseTable.pRate,PurchaseTable.pTotalAmmount,Saletable.sQuantity,Saletable.sRate,Saletable.sTotalAmmount