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?

Somebody just deleted the post i want to Accept, please wrote it back and i will accept it"SELECT * FROM ( SELECT ItemMetaData.KEY, ItemMetaData.VALUE, count(*) 'ItemsCount' FROM ItemMetaData GROUP BY ItemMetaData.KEY ) sub WHERE sub.ItemsCount > 2500 ORDER BY sub.ItemsCount desc;" Having is also working and naturally all other answer will vote up, thank you, but... — czupe, Jul 09 '12 at 15:08

Bill · Accepted Answer · 2012-07-09T15:29:28.490

120

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;

edited Jul 09 '12 at 15:29

answered Jul 09 '12 at 15:04

Bill

4,425
3
21
22

score 16 · Answer 2 · answered Jul 09 '12 at 15:04

16

You should use having with group functions instead of where. E.g.:

select ..., count(*) from ... group by ... having count(*) > 2500;

answered Jul 09 '12 at 15:04

Andrew Logvinov

21,181
6
52
54

score 8 · Answer 3 · answered Jul 09 '12 at 15:04

You do not need to use a subquery - simply use a having clause instead of where clause to filter by an aggregated column.

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

score 5 · Answer 4 · edited Aug 23 '17 at 10:24

Here is the explanation: WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations.

Use WHERE before GROUP BY and HAVING after GROUP BY. It isn't mandatory, but helpuful in most cases.

SELECT 
       ItemMetaData.KEY, ItemMetaData.VALUE, СOUNT(*) 
FROM  ItemMetaData 
GROUP BY
       ItemMetaData.KEY, ItemMetaData.VALUE
HAVING СOUNT(*) > 2500
ORDER BY СOUNT(*) DESC;

SQL COUNT* GROUP BY bigger than,

4 Answers4

Linked

Related