Select where count of one field is greater than one

Question

I want to do something like this   SELECT      FROM db table   WHERE COUNT someField   gt  1   How can I achieve this in MySql

User · Answer

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   gt  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

User · Answer

SELECT username  numb from  Select username  count username  as numb from customers GROUP BY username   as my table WHERE numb  gt  3

User · Answer

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

User · Answer

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  gt  2

User · Answer

Here you go   SELECT Field1  COUNT Field1    FROM Table1   GROUP BY Field1 HAVING COUNT Field1   gt  1 ORDER BY Field1 desc

User · Answer

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

User · Answer

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

User · Answer

Use the HAVING  not WHERE clause  for aggregate result comparison   Taking the query at face value   SELECT      FROM db table  HAVING COUNT someField   gt  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

User · Answer

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

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

Examples related to sql

Examples related to mysql