question

ikramonly avatar image
ikramonly 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
sqljoins
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Andrew_Elias avatar image
Andrew_Elias answered
,try using a group by ProductID
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Andrew_Elias I have tried that too, but no advantage :(
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Very Much @Kev Riley... You are Outstanding :)
0 Likes 0 ·
Please mark it as answer....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.