CODEWITHSUNDEEP
mysql
Rocco The Taco
Rocco The Taco

Reputation: 3797

Use of COUNT to display distinct records with more than 2 occurrences in MySQL

I have a table where I am trying to build a distinct list of all the cities with more than two occurrences in the table. I am trying the current query am am told "function count does not exist"? What am I doing wrong?

SELECT COUNT (city) 
FROM `table1` 
GROUP BY city 
HAVING COUNT (city) >=2

Upvotes: 4

Views: 22778

Answers (2)

waseemwk
waseemwk

Reputation: 1509

Your query is correct you have given a space between COUNT and (City) it must be COUNT(City). that will work fine. Your query should be like this:

SELECT City, COUNT(city) Counts
  FROM `table1` 
 GROUP BY City
HAVING COUNT(city) >=2;

See this SQLFiddle

Upvotes: 12

jaczes
jaczes

Reputation: 1404

USE ALIAS

SELECT COUNT(city) as SOME_TEXT FROM table1 GROUP BY city HAVING SOME_TEXT >=2

Upvotes: -1

Related Questions