[sql] Select where count of one field is greater than one

I want to do something like this:

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

How can I achieve this in MySql?

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

You can also do this with a self-join:

SELECT t1.* FROM db.table t1
JOIN db.table t2 ON t1.someField = t2.someField AND t1.pk != t2.pk

One way

SELECT t1.* 
FROM db.table t1
WHERE exists 
      (SELECT *
      FROM db.table t2 
      where t1.pk != t2.pk 
      and t1.someField = t2.someField)

It should also be mentioned that the "pk" should be a key field. The self-join

SELECT t1.* FROM db.table t1
JOIN db.table t2 ON t1.someField = t2.someField AND t1.pk != t2.pk 

by Bill Karwin give you all the records that are duplicates which is what I wanted. Because some have more than two, you can get the same record more than once. I wrote all to another table with the same fields to get rid of the same records by key fields suppression. I tried

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

above first. The data returned from it give only one of the duplicates, less than 1/2 of what this gives you but the count is good if that is all you want.

As OMG Ponies stated, the having clause is what you are after. However, if you were hoping that you would get discrete rows instead of a summary (the "having" creates a summary) - it cannot be done in a single statement. You must use two statements in that case.

I give an example up on Group By between two table in Sql:

Select cn.name,ct.name,count(ct.id) totalcity from city ct left join country cn on ct.countryid = cn.id Group By cn.name,ct.name Having totalcity > 2

Here you go:

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

For me, Not having a group by just returned empty result. So i guess having a group by for the having statement is pretty important