SQL COUNT* GROUP BY 大于， [英] SQL COUNT* GROUP BY bigger than,

问题描述

我想选择具有出现次数的不同键，这个查询似乎起作用了:

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;

但我也想过滤这些结果，这意味着我只想要 count(*) 大于 2500 的地方，所以只会显示大于 2500 的出现，但是:

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?

推荐答案

HAVING 子句用于聚合

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;

这篇关于SQL COUNT* GROUP BY 大于，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！