How to Select Every Row Where Column Value is NOT Distinct

Question

I need to run a select statement that returns all rows where the value of a column is not distinct (e.g. EmailAddress).

For example, if the table looks like below:

CustomerName     EmailAddress

Aaron            [email protected]

Christy          [email protected]

Jason            [email protected]

Eric             [email protected]

John             [email protected]

I need the query to return:

Aaron            [email protected]

Christy          [email protected]

John             [email protected]

I have read many posts and tried different queries to no avail. The query that I believe should work is below. Can someone suggest an alternative or tell me what may be wrong with my query?

select EmailAddress, CustomerName from Customers

group by EmailAddress, CustomerName

having COUNT(distinct(EmailAddress)) > 1

Answer 1

The below query is faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN

  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)