How to get count of number of rows which have more than X duplicates 1

[wsimmonds]

Hopefully a simple question, but one which I seem to be struggling with at the moment:

As part of a large database system I have a table which contains merely ID numbers, some of which are duplicated. E.g.

id
23592
23911
24950
24950
28700
27910
27910
27910
29100

It's likely that it'll be holding 10s or even 100s of thousands of ID numbers, and what I would like to do is get a count of how many *ID* numbers are duplicated at least X times. For example, if wanted to check where IDs are duplicated:

>2 would return 1 (being 27910)
>1 would return 2 (being 27910 & 24950)

I'm hoping this is possible! I've tried a couple of things so far - this is running from MySQL command line:

SELECT COUNT(DISTINCT id) FROM testtable GROUP BY id HAVING COUNT(id) > 1;

Which for my real data returns a load of 1s, followed by "19806 rows in set (0.11 sec)"

What I'd like to do is simply get that 19806 number! I tried:

SELECT SUM(COUNT(DISTINCT id)) FROM testtable GROUP BY id HAVING COUNT(id) > 1;

That fails with "Invalid use of group function".

Any help would be absolutely hugely appreciated!

Cheers,
Will

 

[TonyGroves]

If you're using MySQL 4.1, you could do:
[tt]
SELECT COUNT(*)
FROM
(
SELECT COUNT(*) c
FROM testtable
GROUP BY id
HAVING c>1
) t
[/tt]

 

[wsimmonds]

Tony,

You have brightened up my day! That works and takes merely 0.13 sec.. I'll accept that!

Superb, thank you.