CODEWITHSUNDEEP
sql-servert-sql
Paul
Paul

Reputation: 3142

SQL Server Group By

I have a table that contains a reference and a name. There are some issues with the data whereby there are more than 2 distinct names for a reference which isn't allowed.

Is there a quick query I can write to find all references that have more than 1 associated name?

Sorry, just to clarify:

I can have many names, and many references but only one name associated with each reference. If there are 2 different names associated with a single reference, I need to show these.

Upvotes: 0

Views: 484

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Well, if the minimal name and maximal name for a particular reference are different, then there must be at least two names associated with that reference:

SELECT Reference, MIN(Name), MAX(Name)
FROM Table
GROUP BY Reference
HAVING MIN(Name) < MAX(Name)

Upvotes: 0

Paul
Paul

Reputation: 3142

I managed it like this:

SELECT ref, COUNT(name)
FROM table
GROUP BY ref
HAVING COUNT(DISTINCT(name)) > 1

Upvotes: 0

sll
sll

Reputation: 62484

Query:

SELECT reference, COUNT(name)
FROM @table
GROUP BY reference
HAVING COUNT(name) > 1

Sample data:

declare @table table(reference varchar(5), name int)
INSERT INTO @table values('Ref#1', 1)
INSERT INTO @table values('Ref#1', 2)
INSERT INTO @table values('Ref#2', 3)
INSERT INTO @table values('Ref#2', 3)
INSERT INTO @table values('Ref#3', 4)

Output:

| Ref#1 | 2 |
| Ref#2 | 2 |

Upvotes: 0

DaveShaw
DaveShaw

Reputation: 52788

You can group it by referenceand then select all the entries that have more than one:

select reference, count(*) 
from myTable
group by reference
having count(*) > 1;

Upvotes: 1

Related Questions