SQL to find duplicate subscribers with the same email address

Question

My company is very soon transitioning to the use of a proprietary subscriber key - we currently use the email address as a subscriber key. I have to do some preliminary investigation into how many people will be affected by this and start reconciling any duplicate accounts.

I would like to see a list of any email addresses that appear more than once, and what their subscriber key is. For example, I am in there several times as a test account, e.g. the following:

SubscriberKey     EmailAddress
xyz                [email protected]
abc                [email protected]
zyx                [email protected]

It's easy to find these - I just used

SELECT SubscriberKey, EmailAddress
FROM _Subscribers
WHERE EmailAddress = '[email protected]'

And it shows me all the different Subscribers with this email address. However, I want a list of all Subscriber Keys with multiple email addresses. I tried this:

SELECT [SubscriberKey], COUNT([EmailAddress]) AS EmailAddress
FROM _Subscribers
GROUP BY [EmailAddress], [SubscriberKey], 
HAVING COUNT([EmailAddress]) > 1

And a couple of variations, but for some reason it returns nothing. Can anyone assist?

Answer 1

I think you need to try something like that:

SELECT SubscriberKey, count(EmailAddress) FROM _Subscribers GROUP BY SubscriberKey HAVING count(EmailAddress) > 1

Just for example, I tried with the following Query and it's worked:

SELECT Name, count(Email) FROM Contact Group By Name HAVING count(Email) > 1

Also, if anything of this doesn't work, you can try to: 1) Make a report with them. 2) Export with DataLoader and put some filters in the newly exported file.

Answer 2

The above query will cause column name error as we need to provide the column names of the data extension. I slightly updated it to the following working version:

SELECT s.SubscriberKey as "SubscriberKey",count(EmailAddress) as "EmailAddress" FROM [_Subscribers] s GROUP BY SubscriberKey HAVING count(EmailAddress) > 1

Answer 3

This should work (I tested it and it worked for me):

SELECT [SubscriberKey], [EmailAddress] FROM _Subscribers s WHERE EXISTS(SELECT [EmailAddress] FROM _Subscribers ss WHERE ss.[EmailAddress] = s.[EmailAddress] GROUP BY [EmailAddress] HAVING COUNT([EmailAddress]) > 1)