Using EXCEPT and DISTINCT in SQL Server

sql, sql-server

Solution

Just use aggregation. If you want the list:

SELECT UserName, EmailAddress
FROM Users
GROUP BY UserName, EmailAddress
HAVING COUNT(*) > 1;

If you just want a count of duplicates, you can count the difference. Unfortunately, SQL Server doesn't allow multiple columns for `COUNT(DISTINCT)` but you can concatenate them. Assuming neither value is ever `NULL`:

SELECT COUNT(*) - COUNT(DISTINCT UserName + ' ' + EmailAddress) as numDuplicates
FROM Users;

Problem

I'm trying to figure out if any users in this table have multiple email addresses. When I run the following two queries, the non-DISTINCT query has more results than the one using DISTINCT. ``` SELECT UserName, EmailAddress FROM Users; SELECT DISTINCT UserName, EmailAddress FROM Users; ``` However, this query does not return any results (presumably since the remaining rows would be identical to one which is in both tables). ``` SELECT UserName, Payment FROM Users EXCEPT SELECT DISTINCT UserName, Payment FROM Users ``` How can I get the users with multiple email addresses?

Original source