Select where count of one field is greater than one
SqlMysqlSql Problem Overview
I want to do something like this:
SELECT *
FROM db.table
WHERE COUNT(someField) > 1
How can I achieve this in MySql?
Sql Solutions
Solution 1 - Sql
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...
Solution 2 - Sql
Here you go:
SELECT Field1, COUNT(Field1)
FROM Table1
GROUP BY Field1
HAVING COUNT(Field1) > 1
ORDER BY Field1 desc
Solution 3 - Sql
SELECT username, numb from(
Select username, count(username) as numb from customers GROUP BY username ) as my_table
WHERE numb > 3
Solution 4 - Sql
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
Solution 5 - Sql
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)
Solution 6 - Sql
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
Solution 7 - Sql
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
Solution 8 - Sql
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.
Solution 9 - Sql
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
Solution 10 - Sql
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.