CODEWITHSUNDEEP
sql-servert-sql
JanT
JanT

Reputation: 2096

SQL Having count distinct with two columns

To make it simple I use AdventureWorks database for this example:

SELECT p.Title
FROM Person.Person p
GROUP BY p.Title
HAVING COUNT(DISTINCT p.LastName) > 1

I struggle to figure out how to add firstname to last name into DISTINCT, so that count distinct would be based on both of those columns rather than just lastname. Issue is that I group by on the column different from those in the COUNT DISTINCT (p.Title here).

Any ideas?

Thanks

Upvotes: 0

Views: 215

Answers (2)

Charlieface
Charlieface

Reputation: 71473

What @GordonLinoff says is a workaround, it does work in this case but it's not necessarily efficient. It also doesn't work if you have dates or numbers.

The more correct way of doing this is to group first by FirstName and LastName also, then group again just over the Title:

SELECT p.Title
FROM (
    SELECT DISTINCT p.Title, p.LastName, p.FirstName
    FROM Person.Person p
) p
GROUP BY p.Title
HAVING COUNT(*) > 1;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Because count(distinct) doesn't take multiple arguments in SQL Server, a work around is to concatenate them together:

HAVING COUNT(DISTINCT CONCAT(p.LastName, ' ', p.FirstName)) > 1

Upvotes: 1

Related Questions