sql query for fetching distinct records from table having count greater than 3

Question

i have a table with coloumn id , name and city

I want to fetch those rows where city is same and the number of rows are greater than 3. Is it possible in single sql query?

Answer 1

Yes, it is pretty much the level of query I use to filter out the "I know SQL" people that dont have a clue about the language.

Lets see whether I get that together.

SELECT city, count() from TABLE GROUP BY city HAVING COUNT() >3

Simple beginner SQL

Not sure mysql supports it ;) But it is apart of the standard for ages.

http://www.w3schools.com/SQL/sql_having.asp

has more explanations.

Answer 2

Yes it is:

SELECT city, COUNT(id) AS [rowcount]
FROM YourTable
GROUP BY city
HAVING COUNT(id) > 3