Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-03-23 : 17:37:16
|
| Hello gentle people,I have a table with Col1 and I'd like to query it's duplicates.I have something like this which counts how many duplicates:select filteredCol1 = (count(Col1) - count(distinct Col1))From Table1I think I need to take the total amount minus from the distinct ones to arrive at the non-distinct values. How can I do this?Thank you. |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-23 : 17:41:37
|
| I think I got it.It's this: select filteredCol1 = (count(Col1) - count(distinct Col1))From Table1Group By Col1having (count(Col1) - count(distinct Col1)) > 0This will give me all the Col1 values and how many times they appear. In my Table, I see 22 records and it says each value shows up 2 times, meaning 22 duplicate entries. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-03-24 : 02:30:13
|
What do you think ist the result of count(distinct Col1)?I think it is always 1.So you don't need to keep your server busy for that.Am I right?GreetingsWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-24 : 10:45:19
|
| No, it's not always 1. It came out to 40-something; I don't recall the exact number at the moment. But I managed to get what I needed.Thank you for your help, though. |
|
|
|
|
|
|
subtracting columsn to find duplicates