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

Serj Sagan · Accepted Answer · 2017-01-12 01:44:04Z

379

This is significantly faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

edited Jan 12, 2017 at 1:44

answered Oct 11, 2013 at 20:26

Serj Sagan

30.6k17 gold badges159 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lou Over a year ago

Hey, I know this answer is 7 years old, but if you're still around would you mind explaining how it works? Solved my problem as well!

Serj Sagan Over a year ago

Using a HAVING here instead of a second SELECT...WHERE causes this to be a single query, instead of the second option which executes that second SELECT...WHERE call many times. See more here: stackoverflow.com/q/9253244/550975

Volodymyr Bobyr Over a year ago

I get the infamous [EmailAddress] must appear in the GROUP BY clause or be used in an aggregate function error. Is the only fix - editing the sql_mode?

Serj Sagan Over a year ago

Well, in the query above, [EmailAddress] IS in the GROUP BY

Seasoned · Accepted Answer · 2012-10-30 19:49:14Z

The thing that is incorrect with your query is that you are grouping by email and name, that forms a group of each unique set of email and name combined together and hence

aaron and [email protected] christy and [email protected] john and [email protected]

are treated as 3 different groups rather all belonging to 1 single group.

Please use the query as given below :

select emailaddress,customername from customers where emailaddress in (select emailaddress from customers group by emailaddress having count(*) > 1)

I like that you also included an explanation about what is wrong with the original query, unlike the accepted answer.

Nisar · Accepted Answer · 2013-07-03 07:21:53Z

27

select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1

edited Jul 3, 2013 at 7:21

answered Jul 3, 2013 at 6:29

Nisar

6,09818 gold badges71 silver badges84 bronze badges

1 Comment

DynamicDan Over a year ago

minor enhancment to show count as "dups": select CustomerName,count(1) as dups from Customers group by CustomerName having count(1) > 1`

Marc · Accepted Answer · 2012-10-30 19:34:08Z

How about

SELECT EmailAddress, CustomerName FROM Customers a WHERE Exists ( SELECT emailAddress FROM customers c WHERE a.customerName != c.customerName AND a.EmailAddress = c.EmailAddress)

Chad · Accepted Answer · 2012-10-30 19:38:15Z

13

Just for fun, here's another way:

;with counts as ( select CustomerName, EmailAddress, count(*) over (partition by EmailAddress) as num from Customers ) select CustomerName, EmailAddress from counts where num > 1

answered Oct 30, 2012 at 19:38

Chad

7,5772 gold badges26 silver badges34 bronze badges

3 Comments

yzorg Over a year ago

+1 for CTE version We shouldn't repeat ourselves in code, why repeat ourselves in SQL if we don't have to anymore.

yzorg Over a year ago

I use _count for the count column (over num). I consistently use underscore when columns happen to collide with SQL keywords like _default, _type, _sum, etc.

Corentin Le Fur Over a year ago

Loved it, much cleaner, and also the only one that was working accross multiple versions of MariaDB

BillyBarbarIan · Accepted Answer · 2019-10-22 22:15:20Z

Rather than using sub queries in where condition which will increase the query time where records are huge.

I would suggest to use Inner Join as a better option to this problem.

Considering the same table this could give the result

SELECT EmailAddress, CustomerName FROM Customers as a Inner Join Customers as b on a.CustomerName <> b.CustomerName and a.EmailAddress = b.EmailAddress

For still better results I would suggest you to use CustomerID or any unique field of your table. Duplication of CustomerName is possible.

mirazimi · Accepted Answer · 2021-04-11 13:47:49Z

SELECT Title, Id FROM dbo.TblNews WHERE (Title IN (SELECT Title FROM dbo.TblNews AS TblNews_1 GROUP BY Title HAVING (COUNT(*) > 1))) ORDER BY Title

sort in title

Collectives™ on Stack Overflow

How to Select Every Row Where Column Value is NOT Distinct

7 Answers 7

4 Comments

1 Comment

1 Comment

Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

1 Comment

1 Comment

Comments

3 Comments

Comments

Comments

Linked

Related