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:

Id NAME EMAIL
1 John John.Smith@abc.com
2 James James.Connor@abc.com
3 Bob Bob.Smith@abc.com
4 Robin Robin.Sharma@abc.com
5 Bob Bob.Smith@abc.com
6 John John.Smith@abc.com

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

Output:

NAME EMAIL cnt
John John.Smith@abc.com 2
Bob Bob.Smith@abc.com 2

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