Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select where count of one field is greater than one

Tags:

sql

mysql

I want to do something like this:

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

How can I achieve this in MySql?

like image 376
stevebot Avatar asked Sep 14 '10 15:09

stevebot


People also ask

Can you use count on more than one field SQL?

You can GROUP BY multiple columns, to get the count of each combination. Show activity on this post. Show activity on this post.

Can you use count in WHERE clause?

SQL SELECT COUNT() can be clubbed with SQL WHERE clause.

How do I get more than one record in SQL?

If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.


1 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...

like image 122
OMG Ponies Avatar answered Sep 22 '22 10:09

OMG Ponies