Count children when parents and children in same table

I'm looking for a good way to count how many children a person has, given that each person has a mother and father attribute. My use case is a little unique in that a person can be either a mother or a father (strange biology).

Given a table like this:

Persons:

person_id   father_id    mother_id
123         321          324
321         324          555
456         321          324

Currently the only index is person_id and all IDs are integer types.

Desired outcome:

person_id     child_count
324           3
321           2
555           1
123           0
321           0
456           0

I have one potential solution:

SELECT parent.person_id, COUNT(child.person_id) AS child_count
FROM person AS parent
LEFT JOIN person AS child ON parent.person_id IN (child.father_id, child.mother_id)
GROUP BY parent.person_id

But it is extremely slow. I have over 1,000,000 people in my table and this query crashes when the limit is higher than about 1,000 or so. I'm looking for a way to do this using far less table scans, perhaps with windowing or a similar technique.



Read more here: https://stackoverflow.com/questions/68492130/count-children-when-parents-and-children-in-same-table

Content Attribution

This content was originally published by Jon Lamb at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: