asked
SQL Join
I have two tables PurchaseDetails and SaleDetails. Both tables have a column with same name i.e 'Quantity'.I want to get balance by getting sum of both columns and then subtract them. I am using query as select (sum(pd.Quantity)-sum(sd.Quantity)) as Balance from PurchaseDetails pd join SalesDetails sd on sd.ProductId=pd.ProductId where pd.ProductId='Pro_1' But It returns a false calculation like '22205', while the actual return should be '158', i have tried this query also but it also gives the same erroneous results select sum(pd.quantity)-sum(sd.quantity) from PurchaseDetails pd,salesdetails sd where pd.ProductId=sd.ProductId and pd.productid='Pro_1' please help me
This web site works by voting. For all helpful answers below, indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
You are joining on the productid, so every purchase row will join to every sales row (for that product). What you want is to total the purchases and subtract the total of the sales. select ProductPurchase.PurchaseQuantity-ProductSales.SalesQuantity from (select ProductId, sum(Quantity) as PurchaseQuantity from PurchaseDetails group by ProductId) ProductPurchase join (select ProductId, sum(Quantity) as SalesQuantity from SalesDetails group by ProductId) ProductSales on ProductPurchase.ProductId=ProductSales.ProductId where ProductPurchase.ProductId='Pro_1'
16 People are following this question.
