CODEWITHSUNDEEP
mysqlsql
Ben G
Ben G

Reputation: 26771

Only returning rows when there is a certain count of a given column

I'm trying to make a SELECT query which only returns rows when a column (call it My_Column) has only one distinct value, that is when COUNT(DISTINCT My_Column) = 1. If there are multiple distinct values of My_Column, I don't want any rows returned. How can I do that?

Upvotes: 1

Views: 66

Answers (4)

Naftali
Naftali

Reputation: 146300

SELECT a.* 
FROM table a
WHERE 1 = (SELECT COUNT(DISTINCT My_Column) FROM table);

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

SELECT t.*
  FROM YOUR_TABLE t
HAVING COUNT(DISTINCT t.my_column) = 1

Be aware of how MySQL handles GROUP BY and HAVING with Hidden Columns.

Upvotes: 3

AJ.
AJ.

Reputation: 28174

Try this:

SELECT COUNT(My_Column), My_Column FROM tbl GROUP BY My_Column HAVING COUNT(My_Column)=1

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79594

SELECT ...
GROUP BY foo
HAVING COUNT(DISTINCT My_Column) = 1;

Upvotes: 1

Related Questions