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

问题描述

  SELECT ItemMetaData.KEY，ItemMetaData 
 
我想选择具有出现编号的不同关键字， .VALUE，count（*）
 FROM ItemMetaData 
 GROUP BY ItemMetaData.KEY 
 ORDER BY count（*）desc; 
  

但我也想过滤这些结果，这意味着我只想在count（*）大于2500，所以只显示2500以上的情况，但是：

  SELECT * 
 FROM 
（
 SELECT ItemMetaData.KEY，ItemMetaData.VALUE，count（*）
 FROM ItemMetaData 
 GROUP BY ItemMetaData.KEY 
 ORDER BY count（*）desc 
）as result WHERE count （*）> 2500; 
  

不幸的是，这个查询导致语法错误。你能帮我达到我的要求吗？

pre> SELECT ItemMetaData.KEY，ItemMetaData.VALUE，count（*）
FROM ItemMetaData
Group By ItemMetaData.KEY，ItemMetaData.VALUE
HAVING count（ *）> 2500
ORDER BY count（*）desc;


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?

解决方案

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屋！