CODEWITHSUNDEEP
sql-serversql-server-2008
sseager
sseager

Reputation: 21

List the records that have multiple gender values per individual

I have two columns of interest in a table. They are 'PersonID' which is a unique ID for each individual and a 'Gender' column.

My table has multiple rows for each individual as each row reflects a policy they have under their name. However, for each individual I have noticed some data quality issues, whereby their gender has been recorded as 'Male' for some of the rows and then 'Female' for others

How do I compile a list of all the rows that show the 'PersonIDs' that have been recorded as both Male and Female?

So an example of how my table looks at the moment is like this: 4439; 1 4439; 1 4439; 1 4439; 1 4439; 1 4439; 0 4439; 0

where 4439=PersonId and 1=Male (0=female)

I just want a list gives me all the PersonIDs that have both 1 and 0 in the gender field

Upvotes: 0

Views: 374

Answers (4)

artm
artm

Reputation: 8584

Update:

SELECT DISTINCT personid FROM(
SELECT PersonID
, RANK() OVER (partition by personid order by gender) as rnk
FROM table t
) i 
WHERE i.rnk > 1

instead of below as @Dan pointed out.

SELECT PersonID
FROM table t
WHERE t.gender = '0' OR t.gender = '1'
GROUP BY PersonID
HAVING COUNT(*) > 1

If you need additional info:

SELECT p.Name, p.lastName, p.DOB 
FROM table p
WHERE p.PersonID IN
(
    SELECT PersonID
    FROM table t
    WHERE t.gender = '0' OR t.gender = '1'
    GROUP BY PersonID
    HAVING COUNT(*) > 1
) i

Upvotes: 1

Dan
Dan

Reputation: 10680

You simply need to count the number of distinct gender codes on each person:

SELECT PersonID
FROM MyTable
GROUP BY PersonID
HAVING COUNT(DISTINCT Gender) > 1

Upvotes: 0

JayValkyr
JayValkyr

Reputation: 131

Try this.

SELECT PersonID
FROM
(
    SELECT PersonID,Gender
    FROM table
    GROUP BY PersonID,Gender
) as DATA
GROUP BY PersonID
HAVING COUNT(*) > 1

Upvotes: 0

K.K
K.K

Reputation: 366

This resolves you,

SELECT ID FROM POLICYTABLE 
WHERE GENDER=0 OR GENDER=1  
GROUP BY ID 
HAVING COUNT(*)>1

Upvotes: 0

Related Questions