Adding a threshold to a COUNT() query

[l008com]

I've got a simple query that counts distinct items in a table

SELECT COUNT(DISTINCT flag_rest_spam.msgid) FROM flag_rest_spam

What I want to do is add a threshold to the count. What I mean is I want to count distinct items in the table, but i only one to count items that have more than N number of items. I'm not sure how to do this?

[laserlight]

I believe it should be something like this:

SELECT COUNT(DISTINCT flag_rest_spam.msgid) AS msgid_count FROM flag_rest_spam HAVING msgid_count > $N

[l008com]

laserlight;10893822 wrote:

I believe it should be something like this:

SELECT COUNT(DISTINCT flag_rest_spam.msgid) AS msgid_count FROM flag_rest_spam HAVING msgid_count > $N

That doesn't seem to work. Its actually returning the same number as the original query.

[laserlight]

Ah yes, the DISTINCT would change that. Try this instead:

SELECT COUNT(*) FROM (SELECT COUNT(flag_rest_spam.msgid) AS msgid_count FROM flag_rest_spam HAVING msgid_count > $N)

[Sxooter]

Subselects can be slow in many mysql versions. this might be better:

SELECT COUNT(DISTINCT flag_rest_spam.msgid) FROM flag_rest_spam

select count() from flag_rest_spam group by msgid having count() > somenumber

[djjjozsi]

complementing the previous post:

this query makes a list of an item where msgid is found many times than 4,

and you can use its number as CountOfmsgid...

SELECT flag_rest_spam.msgid, Count(flag_rest_spam.msgid) AS CountOfmsgid
FROM flag_rest_spam
GROUP BY flag_rest_spam.msgid
HAVING (CountOfmsgid > 4);