Calculate the number of direct children in a hierarchy

Question

I have a simple hierarchy of categories in an SQLite3 database, each row storing a parent ID or NULL as appropriate.

I would like to find out whether a particular category is a leaf, essentially by determining, for each row, whether it has a parent ID defined. Or rather, determining a count of child rows for each row.

---

Table definition:

CREATE TABLE category (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    name TEXT NOT NULL
    parent_id INTEGER DEFAULT NULL
);

Sample data:

id          name        parent_id 
----------  ----------  ----------
34          People      
35          Countries   
36          USA         35
37          Pop         36
38          Rock        36
39          Japan       35
40          Pop         39
42          Rock        39
43          J-Pop       40

Desired output:
The original data plus a count of how many subcategories (children) each row has.

id          name        parent_id   direct_children
----------  ----------  ----------  ---------------
34          People                  0
35          Countries               2
36          USA         35          2
37          Pop         36          0
38          Rock        36          0
39          Japan       35          2
40          Pop         39          1
42          Rock        39          0
43          J-Pop       40          0

---

This appears potentially simple(?), but as I'm generally lost beyond simple JOINs, I so far haven't got very far with this. I've checked similar questions, but they either seem to be joining across tables, or want to do a more complex count of all children across the hierarchy, not just the direct children rows.

Changing the table schema is a possibility (e.g. if a child_id or child_count) would be necessary, but I'd rather not.

Any input would most appreciated.

Answer 1

You could probably do that with a subquery:

select  c.*
,       (select count(*) from category c2 where c2.parent_id = c.id) 
            as direct_children
from    category c

Or a join:

select  parent.id
,       parent.name
,       parent.parent_id
,       count(child.id) as direct_children
from    category parent
left join    
        category child
on      child.parent_id = parent.id
group by
        parent.id
,       parent.name
,       parent.parent_id