Counting entries where relationship to each other is not determined

Question

I want to count a list of entries in my database that all have specified relationships to one person, but I need to count by their relationship to each other - in other words, they are listed as sons and daughters, brothers and sisters of the main person in the house - I need to count the number of siblings.

My approach was to try to count all the households with more than one son or daughter or any household with a son and a daughter or any household with a brother and a sister, more than one brother or more than one sister. However when I try to put this into SQL I'm not getting anywhere.

SELECT FirstName, Surname 
FROM individuals
WHERE (RelationshipToHead = 'Son' AND RelationshipToHead = 'Daughter') 
   OR (RelationshipToHead = 'Son' AND RelationshipToHead = 'Son') 
   OR (RelationshipToHead = 'Daughter' AND RelationshipToHead = 'Daughter') 
   OR (RelationshipToHead = 'Brother' AND RelationshipToHead = 'Sister') 
   OR (RelationshipToHead = 'Sister' AND RelationshipToHead = 'Sister') 
   OR (RelationshipToHead = 'Brother' AND RelationshipToHead = 'Brother')

This merely returns all entries that contain son daughter brother or sister - not the ones with more than one of each in each family.

Could you suggest a better query?

score 1 · Accepted Answer · 2011-11-16T18:06:05.210

1

The where clause only compares values within each row, which is why your query isn't behaving as you want. I suggest:

SELECT count(distinct AddressID) HouseholdsWithSiblings, 
       sum(NumberOfSiblings) TotalSiblings
FROM   (SELECT AddressID, count(*) NumberOfSiblings 
        FROM individuals
        WHERE RelationshipToHead in ('Son','Daughter')
        GROUP BY AddressID
        HAVING count(*) > 1
        UNION ALL
        SELECT AddressID, count(*)+1 NumberOfSiblings 
        FROM individuals
        WHERE RelationshipToHead in ('Brother','Sister')
        GROUP BY AddressID) v;

edited Nov 16 '11 at 18:06

answered Nov 16 '11 at 17:23

Thanks Mark, that's only throwing up those listed as Brothers or Sisters, and the return I need is just the number. – nevermind Nov 16 '11 at 17:29
@staceym, does that mean that the query is returning what you need, or not? If not, what's missing? Do you need the number of siblings in each household, or the number of households with multiple siblings? – Nov 16 '11 at 17:33
I need the total number of siblings, so it doesn't need the first name or last name specifically, I'm just using that to make sure that two entries I know are that are only children are not coming up in the results. Thanks for your help. – nevermind Nov 16 '11 at 17:37
@staceym, I see from your earlier question that the individuals table includes an AddressID which uniquley identifies households. I have updated my query accordingly - I *think* this should return what you are looking for, but let me know. – Nov 16 '11 at 17:55
Yes that works great - one last bit though - is there a way to get a total of all the siblings from this table? There are over 1 million entries... (it's quite a big Database) thanks again . – nevermind Nov 16 '11 at 18:00
Genius! Thanks so much. Hopefully some day I'll be able to understand everything to do with SQL, but it's not so easy at the start. – nevermind Nov 16 '11 at 18:08
1

That's a table alias; some flavours of SQL (including MySQL) require them after inline views, others don't. – Nov 17 '11 at 15:51

Counting entries where relationship to each other is not determined

1 Answers1