count - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

count

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-26 : 13:18:15

I need some help with count function.

I am counting the number of records that are duplicate in a given table. For this example lets say there are 20 records that have exactly 2 or more records identitcal to it.

Is there a query that I can write to give me a result of the number 20.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-26 : 13:21:44

will this do?

select count(col1)
from MyTable
group by col1
having count(col1) >= 2

Go with the flow & have fun! Else fight the flow

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-26 : 13:33:45

No that won't work because that will give me '2' for each record where the record is duplicated in Col 1. Consider the following for col 1:

111
111
222
333
444
555
555
666
666
777
888
888

So in this example I want the number that is returned to be 4 becuase there are 4 instances where there is duplicated data.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-26 : 13:36:23

select count(*)
from
(
select count(col1) as col1Count
from MyTable
group by col1
having count(col1) >= 2
) t

Go with the flow & have fun! Else fight the flow

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-26 : 13:45:00

I figured out how to do it..

Select count(*)
from
(Select count(col 1)as a from MY TABLE group by col 1 having count(Col_1)>1) as b.

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-26 : 13:54:41

does that mean you can read as well?