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?

3 Answers 3


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.


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


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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .