Help with a select count query

Question

I want to show all cities that have have a count > 5. I have tried to limit my results anything over a count of 5 but it isn't working.

SELECT
user.city,
Count(user.city) AS cnt
FROM user
Inner Join zip ON zip.zip = user.zip
WHERE cnt > 5
GROUP BY user.city
WHERE cnt > 5 **<--------------- It fails here**

cnt has already been defined in the field list so why doesn't work?

Answer 1

Try using the HAVING statement:

For example:

select title, AVG(salary)
from employee_data
GROUP BY title 
HAVING AVG(salary) > 100000;

Answer 2

Try HAVING

SELECT  user.city,
        COUNT(user.city) AS cnt
FROM user
INNER JOIN zip ON zip.zip = user.zip
GROUP BY user.city
HAVING COUNT(user.city) > 5

Answer 3

you must use having cnt > 4 when you are grouping

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Answer 4

I got it. All I needed was the "having" clause.