SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Counting Duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/18/2013 :  11:47:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3571 Posts

Posted - 06/18/2013 :  12:02:11  Show Profile  Reply with Quote
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
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.
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/18/2013 :  14:27:58  Show Profile  Reply with Quote
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..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3571 Posts

Posted - 06/18/2013 :  15:42:30  Show Profile  Reply with Quote
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.
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/18/2013 :  15:54:17  Show Profile  Reply with Quote
I see.. thanks!

==============================
I'm here to learn new things everyday..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000