CODEWITHSUNDEEP
sql-server-2005t-sqlstored-procedures
Bertan
Bertan

Reputation: 27

T-Sql find duplicate row values

I want to write a stored procedure.

In that stored procedure, I want to find duplicate row values from a table, and calculate sum operation on these rows to the same table.

Let's say, I have a CustomerSales table;

ID  SalesRepresentative Customer  Quantity 
1   Michael             CustA        55     
2   Michael             CustA        10

and I need to turn table to...

ID  SalesRepresentative Customer  Quantity 
1   Michael             CustA        65     
2   Michael             CustA        0

When I find SalesRepresentative and Customer duplicates at the same time, I want to sum all Quantity values of these rows and assign to the first row of a table, and others will be '0'.

Could you help me.

Upvotes: 0

Views: 725

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

To aggregate duplicates into one row:

SELECT min(ID) AS ID, SalesRepresentative, Customer
      ,sum(Quantity) AS Quantity
FROM   CustomerSales 
GROUP  BY SalesRepresentative, Customer
ORDER  BY min(ID)

Or, if you actually want those extra rows with 0 as Quantity in the result:

SELECT ID, SalesRepresentative, Customer
      ,CASE 
          WHEN (count(*) OVER (PARTITION BY SalesRepresentative,Customer)) = 1
             THEN Quantity
          WHEN (row_number() OVER (PARTITION BY SalesRepresentative,Customer
                                   ORDER BY ID)) = 1 
             THEN sum(Quantity) OVER (PARTITION BY SalesRepresentative,Customer)
          ELSE 0
       END AS Quantity
FROM   CustomerSales
ORDER  BY ID

This makes heavy use of window functions.

Alternative version without window functions:

SELECT min(ID) AS ID, SalesRepresentative, Customer, sum(Quantity) AS Quantity
FROM   CustomerSales 
GROUP  BY SalesRepresentative, Customer

UNION ALL 
SELECT ID, SalesRepresentative, Customer, 0 AS Quantity
FROM   CustomerSales c
GROUP  BY SalesRepresentative, Customer
LEFT   JOIN (
    SELECT min(ID) AS ID
    FROM   CustomerSales 
    GROUP  BY SalesRepresentative, Customer
   ) x ON (x.ID = c.ID)
WHERE  x.ID IS NULL
ORDER  BY ID

Upvotes: 2

Related Questions