Consider the below table
Declare @t Table(name varchar(20), email varchar(20))
insert into @t values ('name1', '1stEmail@email.com')
insert into @t values ('name1', '2ndEmail@email.com')
insert into @t values ('name1', '3rdEmail@email.com')
insert into @t values ('name2', '1stEmail@email.com')
insert into @t values ('name3', '1stEmail@email.com')
insert into @t values ('name3', '2ndEmail@email.com')
insert into @t values ('name4', '1stEmail@email.com')
RowNumber approach to find the duplicate email entries Select distinct name,count(email)+1
From
(
Select
Rn = Row_Number() Over(Partition By name Order By (Select 1))
,name,email
From @t
)x
Where Rn > 1
Group By name
/* Result */
name EmailCount
name1 3
name3 2
We are using the partition by clause to perform the partition which will yield the below
Rn name email
1 name1 1stEmail@email.com
2 name1 2ndEmail@email.com
3 name1 3rdEmail@email.com
1 name2 1stEmail@email.com
1 name3 1stEmail@email.com
2 name3 2ndEmail@email.com
1 name4 1stEmail@email.com
From the result generated we can make out that, Rn with value > 1 has multiple entries.