Select where count of one field is greater than one

Use the HAVING, not WHERE clause, for aggregate result comparison.

Taking the query at face value:

SELECT * 
  FROM db.table 
HAVING COUNT(someField) > 1

Ideally, there should be a GROUP BY defined for proper valuation in the HAVING clause, but MySQL does allow hidden columns from the GROUP BY...

Is this in preparation for a unique constraint on someField? Looks like it should be...

Here you go:

SELECT Field1, COUNT(Field1)
  FROM Table1 
 GROUP BY Field1
HAVING COUNT(Field1) > 1
ORDER BY Field1 desc

SELECT username, numb from(
Select username, count(username) as numb from customers GROUP BY username ) as my_table
WHERE numb > 3

Related