Counting Similar Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Counting Similar Names

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-12-14 : 12:00:01

I've got the Table EmployeeInfo that contains FIRSTNAME and LASTNAME for all employees.

How would I write a query to return the number of duplicates?


Count(EmployeeInfo.FIRSTNAME+' '+EmployeeInfo.LASTNAME) AS 'Duplicate'

Everything I try keeps throwing different kinds of errors.

Thanks,
~Joe

Avoid Sears Home Improvement

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-14 : 12:56:08

Give this a try

SELECT [Duplicates] = count(*)
FROM
(

select FirstName,LastName,[recs] =count(*)
from yourTable
group by FirstName,LastName
having count(*) > 1
) a

Jim

Everyday I learn something that somebody else already knew

jp2code
Posting Yak Master

175 Posts

Posted - 2009-12-14 : 13:58:46

Hi Jim,

That gets me part of the way. I now know that there are 50 duplicates.

To identify the duplicate records, I modified the statement as follows:

select firstname ,lastname, [duplicates]=count(*) from
(
select firstname, lastname, [recs]=count(*) from EmployeeInfo
group by firstname, lastname
having 1<count(*)
) a
group by firstname, lastname

In my application, I want to present to HR when they have duplicate records so that they can fix it on their end. They need to know which employee has the duplicates.

Thanks,
~Joe

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources