Click here to Skip to main content
15,305,361 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to combine all duplicates in my SQL table
1	 1            op1          1        
2	 1            op1          1
3	 2            op2          1
4	 2            op2          1
5	 2            op4          1
6	 1            op1          1

my result shows duplicates in months and Operators. Here 1 and 2 are the date (months) column and op1 is the operators. I want to combine for instance, in month 1, opt1 has 3 and in month 2 op2 has 2 and op4 has only 1. The above statement is executed and this is what I get from it.
How can I remove duplicates from my table that have the same values across my table I tried many sql statements but still have not been able to figure this out? I would appreciate any help to figure this out

What I have tried:

SQL
SELECT Month(Date) as [Date], Operator, COUNT(*) FROM tbl_xxxxxx
GROUP BY Date, Operator
ORDER BY Operator
Posted
Updated 16-Feb-21 2:43am
v2
Comments
Maciej Los 16-Feb-21 6:15am
   
What do you mean by "duplicates"?

1 solution

Quote:
How can I remove duplicates from my table that have the same values across my table


If you would like to delete duplicates based on date and operator, you can try this:
SQL
DELETE f
FROM foo f INNER JOIN 
(
  SELECT id, MONTH([Date]) m, ROW_NUMBER() OVER (PARTITION BY Operator, MONTH([Date]) ORDER BY id) rn
  FROM foo
) h ON f.id = h.id 
WHERE h.rn >1;


After query execution, your source table will contain "unique" values:
Id 	Date 	Operator
1 	2021-01-01 	op1
3 	2021-02-01 	op2
5 	2021-02-03 	op4


db<>fiddle[^]

Note: i was asking you about the definition of duplicates. Until now, you didn't explain that. So, this is my best guess.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900