Counting Duplicates - 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

General SQL Server Forums

New to SQL Server Programming

Counting Duplicates

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-18 : 11:47:23

Hey Guys

Quick & simple question, i want to determine, if i got any duplicate records within my data set

I am unable to eye ball as there is 174k rows

I just want to find out how many duplicate account_id i have

This is my query

--Final Build with Fdmsaccount ParentID--
Select distinct
#Address.FDMSAccountNo,
#Address.[DBA Name],
#Address.[Legal Name],
#Address.Street,
#Address.[MM3-DBA-ADDR2],
#Address.[MM3-DBA-ADDR4],
#Address.City,
#Address.County,
#Address.Postalcode,
#Address.Country,
#Address.Phone,
#Address.Open_Date,
#Address.Cancel_Date,
#Address.Last_Post_Date,
#Address.[BoS Owner],
#Address.RecordTypeId,
u.FDMSAccountNo,
#Address.Parentsfid,
u.account_id,
u1.account_id as parentid
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno
order by Open_Date desc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 12:02:11

[code]SELECT u.account_id
FROM #Address
INNER JOIN #Update u ON #Address.FDMSAccountNo = u.FDMSAccountNo
LEFT JOIN #Update u1 ON #Address.Parentsfid = u1.fdmsaccountno
GROUP BY u.account_id
HAVING COUNT(*) > 1[/code]That will give you duplicate account id's (assuming u.account_id is what you are trying to find duplicates of).

You can then use that information to retrieve all the other columns in your query if you need to.

shan007
Starting Member

17 Posts

Posted - 2013-06-18 : 14:27:58

Aggregate function missed in above script. Below script should work good.

SELECT u.account_id, COUNT(*)
FROM #Address
INNER JOIN #Update u ON #Address.FDMSAccountNo = u.FDMSAccountNo
LEFT JOIN #Update u1 ON #Address.Parentsfid = u1.fdmsaccountno
GROUP BY u.account_id
HAVING COUNT(*) > 1

==============================
I'm here to learn new things everyday..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 15:42:30

quote:
Originally posted by shan007

Aggregate function missed in above script. Below script should work good.

SELECT u.account_id, COUNT(*)
FROM #Address
INNER JOIN #Update u ON #Address.FDMSAccountNo = u.FDMSAccountNo
LEFT JOIN #Update u1 ON #Address.Parentsfid = u1.fdmsaccountno
GROUP BY u.account_id
HAVING COUNT(*) > 1

==============================
I'm here to learn new things everyday..

If you don't care about how many duplicates exist, i.e., you are only trying to answer the question "Are there duplicates or not", you don't need the COUNT(*) in the SELECT clause.

shan007
Starting Member

17 Posts

Posted - 2013-06-18 : 15:54:17

I see.. thanks!

==============================
I'm here to learn new things everyday..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources