CODEWITHSUNDEEP
sqlrsqldf
Hack-R
Hack-R

Reputation: 23214

Trouble Finding ID's with Duplicate Fields

My data looks like this:

ID  Email
1   [email protected]
2   [email protected]
3   [email protected]
4   [email protected]
5   [email protected]
6   [email protected]

There should be exactly 1 email per ID, but there's not.

> dim(data)
[1] 5071    2
> length(unique(data$Person_Onyx_Id))
[1] 5071
> length((data$Email))
[1] 5071
> length(unique(data$Email))
[1] 4481

So, I need to find the ID's with duplicated email addresses.

Seems like this should be easy, but I'm striking out:

> sqldf("select ID, count(Email) from data  group by ID having count(Email) > 1")
[1] ID count(Email)  
<0 rows> (or 0-length row.names)

I've also tried taking off the having clause and sending the result to an object and sorting the object by the count(Email)... it appears that every ID has count(Email) of 1...

I would dput the actual data but I can't due to the sensitivity of email addresses.

Upvotes: 0

Views: 52

Answers (2)

HLGEM
HLGEM

Reputation: 96552

Are you also sure you don't have the opposite condition, multiple ids with the same email?

select Email, count(*)
from data
group by Email
having count(*) > 1;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

My guess is that you have NULL emails. You could find this by using count(*) rather than count(email):

select ID, count(*)
from data
group by ID
having count(*) > 1;

Upvotes: 2

Related Questions