MySQL fetch a field where the field exists > x times in table

Question

I'm trying to do the following in MySQL:

SELECT DISTINCT field
FROM table
WHERE COUNT(field) > 10

Which fails with: 1111 - Invalid use of group function (from what I understand, you can't use group functions such as COUNT in the where clause?)

What is the proper way of selecting (distinct) all fields which are present in at least N rows? Is this something I'll have to do externally with say, PHP?

Thanks!

Answer 1

use:

SELECT DISTINCT field
  FROM table
HAVING COUNT(field) > 10

You can't use aggregate functions outside of a subquery in the WHERE clause. Which is why you need to use the HAVING clause. Also keep in mind that COUNT counts non-null values...

Ideally, there should be a GROUP BY clause as well. But MySQL is rather lax about that.

To compare all the columns

...you're going to have to add a GROUP BY clause that lists all those columns--there's no shorthand in this case. Then change the HAVING clause to COUNT(*) > 10.