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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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…

Method 2

Here you go:

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

Method 3

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

Method 4

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

Method 5

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)

Method 6

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

Method 7

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

Method 8

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.

Method 9

Without HAVING

SELECT COL,TOTAL
FROM (SELECT SPORT, COUNT(COL) AS TOTAL FROM db.table GROUP BY SPORT)
WHERE TOTAL > 100 ORDER BY TOTAL

or with HAVING

SELECT COL, COUNT(COL) AS TOTAL
FROM db.table
GROUP BY SPORT HAVING TOTAL > 100
ORDER BY COL

Method 10

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0