How select row where some column has more than 1 distinct value?

Question

I have to find if there are Rows where a Name has more than one distinct Family.

Note: Name and Family can be duplicate.

ID      Name        Family
1       ABC         XYZ
2       DEF         XYZ
3       ABC         UVW
4       ABC         RST
5       DEF         RST
6       GHI         UVW

The expected Output should be

Name
ABC
DEF

Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Lixas, Oct 23 '18 at 11:36
I don't want to find duplicate rows, I want to find if a Name has more than one Family — Pankaj Mahato, Oct 23 '18 at 11:38

score 1 · Accepted Answer · answered Oct 23 '18 at 11:38

1

I think you could do this;

SELECT Name, COUNT(DISTINCT Family)
FROM [table]
GROUP BY Name
HAVING COUNT(DISTINCT Family) > 1

answered Oct 23 '18 at 11:38

James Cooke

1,221
1
14
27

1

better use an alias for "count (distinct Family)" and call "having" with that alias, for readability – mangusta Oct 23 '18 at 12:10

How select row where some column has more than 1 distinct value?

1 Answers1