'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 |
|---|
