Count Duplicates Help - 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 2012 Forums

Transact-SQL (2012)

Count Duplicates Help

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-13 : 18:18:04

I have a table that I need to count duplicate social security numbers. This is what I came up with (see below). I also need to see other fields in this same query. This query isn't working ...kinda of a newbie and I'm not sure what I did wrong.


SELECT Clientid, MRNum, FirstName, LastName, COUNT(SSN)
FROM Patient
GROUP BY SSN
HAVING (COUNT(SSN) > 1)

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-13 : 18:25:31

[code]
;
WITH DupeSSN (SSN, DupeCount)
AS
(
SELECT SSN, COUNT(*) AS DupeCount
FROM Patient
GROUP BY SSN
HAVING COUNT(*) > 1
)
SELECT Clientid, MRNum, FirstName, LastName, p.SSN, d.DupeCount
FROM Patient p
JOIN DupeSSN d ON p.SSN = d.SSN;
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-13 : 18:30:07

This is almost what I need! ..thank you. What I need now is when Clientid = 5051....Sorry, didn't mention this part because i thought I could just stick it in there. Seems no matter where I stick in the WHERE clause SQL bombs...

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-13 : 18:36:09

[code]
WITH DupeSSN (SSN, DupeCount)
AS
(
SELECT SSN, COUNT(*) AS DupeCount
FROM Patient
WHERE Clientid = 5051
GROUP BY SSN
HAVING COUNT(*) > 1
)
SELECT Clientid, MRNum, FirstName, LastName, p.SSN
FROM Patient p
JOIN DupeSSN d ON p.SSN = d.SSN;
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-13 : 18:38:34

That doesn't work ....In this new query the SSN now is the actual SSN number (not a count) and I still get all the different clientid's

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-13 : 18:44:31

I don't understand what you mean, probably sample data will help, but you could try adding the WHERE clause below the JOIN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2015-02-14 : 01:13:13

SELECT Clientid, MRNum, FirstName, LastName, b.mycount
FROM Patient A
Cross apply
(
SELECT aa.ssN,count(*) as mycount FROM Patient aa
Where as.ssn = a.ssn
Group by aa.ssn
Having count(*) > 1
) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources