x

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

untitled.png (16.5 kB)
more ▼

asked Dec 29, 2015 at 09:37 AM in Default

avatar image

Ayman
31 1 1 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
 
more ▼

answered Dec 29, 2015 at 01:31 PM

avatar image

sp_lock
10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2219
x461
x48
x15
x14

asked: Dec 29, 2015 at 09:37 AM

Seen: 231 times

Last Updated: Jan 03, 2016 at 04:01 AM

Copyright 2018 Redgate Software. Privacy Policy