question

Ayman avatar image
Ayman asked

SQL query creates duplicate values

When I execute the query below, I get some duplicated values when I have data belong to same Purchase_ID in table Stock_Purchase_Details SELECT p.Purchase_ID, pd.*, s.*, SUM(pd.Discount) OVER (PARTITION BY p.Purchase_ID) as totaldis FROM dbo.Stock_Purchase p INNER JOIN dbo.Stock_Purchase_Details pd ON p.Purchase_ID = pd.Purchase_ID INNER JOIN dbo.Supplier s ON p.Supplier_ID = s.Supplier_ID; ![database diagram ][1] [1]: /storage/temp/3131-untitled.png
sql-server-2008querysuminner joindelete-duplicates
untitled.png (16.2 KiB)
10 |1200

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

1 Answer

·
sp_lock avatar image
sp_lock answered
Have you tried just a simple SUM of the discount with the PurchaseID grouped. SELECT p.Purchase_ID, SUM(pd.Discount) as totaldis FROM dbo.Stock_Purchase p INNER JOIN dbo.Stock_Purchase_Details pd ON p.Purchase_ID = pd.Purchase_ID INNER JOIN dbo.Supplier s ON p.Supplier_ID = s.Supplier_ID GROUP BY p.Purchase_ID
10 |1200

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

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.