Finding duplicates in a table by grouping columns SQL Server

The GROUP By clause is used in a SELECT query to group data in rows based on certain columns.
It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set.

To find duplicates in SQL Server table using GROUP BY clause based on multiple columns say name and email as below:

1 John
2 James
3 Bob
4 Robin
5 Bob
6 John

SELECT name, email, COUNT(*) AS cnt FROM userdetails
GROUP BY name, email


John 2
Bob 2

SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.