I want to do something like this:
SELECT *
FROM db.table
WHERE COUNT(someField) > 1
How can I achieve this in MySql?
Add a comment
|
11 Answers
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...
answered Sep 14, 2010 at 15:45
-
13Needs a
GROUP BYsurely (unless this is some MySQL non standard thing)? Commented Sep 14, 2010 at 15:47 -
@Martin Smith: Took the query at face value; addressed GROUP BY issue (incl. hidden columns feature). Commented Sep 14, 2010 at 15:50
-
"Looks like it should be..." Why? I am in need of education on this :)– DaveCommented Sep 14, 2010 at 15:51
-
So this will return the whole table if it contains more than 2 non null
someFieldvalues or an empty result set if it doesn't. Commented Sep 14, 2010 at 15:52 -
@Dave: If you were in a position where you had to periodically check & correct bad data, wouldn't you want to stop the situation from happening in the first place? MySQL implements a unique constraint as an index - for more info see the CREATE INDEX documentation Commented Sep 14, 2010 at 15:56
Here you go:
SELECT Field1, COUNT(Field1)
FROM Table1
GROUP BY Field1
HAVING COUNT(Field1) > 1
ORDER BY Field1 desc
answered Dec 18, 2018 at 4:02
SELECT username, numb from(
Select username, count(username) as numb from customers GROUP BY username ) as my_table
WHERE numb > 3
-
3the only caveat here (at least in 5.1.46-community MySQL Community Server (GPL)) is that "Every derived table must have its own alias", that will make you sql look like: SELECT username, numb from( Select username, count(username) as numb from customers GROUP BY username ) as my_table WHERE numb > 3– D_KCommented Aug 10, 2012 at 11:57
-
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
answered Sep 14, 2010 at 15:57
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)
answered Sep 14, 2010 at 15:45
Maybe:
SELECT *
FROM [Table]
where field IN (
select field
from [Table]
group by field
having COUNT(field)>1 )
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
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
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.
answered Sep 14, 2010 at 15:47
-
1Not entirely true - use the GROUP BY to manipulate what the HAVING is using. Commented Sep 14, 2010 at 15:48
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
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.
-
4