SQL COUNT* GROUP BY bigger than,

Question

I want to select the distinct keys with the occurence number, this query seems functionate:

SELECT ItemMetaData.KEY, ItemMetaData.VALUE, count(*)  FROM ItemMetaData  GROUP BY ItemMetaData.KEY  ORDER BY count(*) desc; 

But I also want to filter these result, meaning I want only where count(*) is greater than 2500 so only bigger than 2500 occurence will shown, but:

SELECT *  FROM  (     SELECT ItemMetaData.KEY, ItemMetaData.VALUE, count(*)      FROM ItemMetaData      GROUP BY ItemMetaData.KEY      ORDER BY count(*) desc ) as result WHERE count(*)>2500; 

Unfortunately this query results in a syntax error. Can you help me achieve my requirement?

Answer 1

HAVING clause for aggregates

SELECT ItemMetaData.KEY, ItemMetaData.VALUE, count(*)  FROM ItemMetaData  Group By ItemMetaData.KEY, ItemMetaData.VALUE HAVING count(*) > 2500 ORDER BY count(*) desc;