Calculate the number of direct children in the hierarchy

Question

I have a simple category hierarchy in a SQLite3 database, each row stores a parent id or NULLas needed.

I would like to know if a particular category is a sheet, essentially by determining for each row whether it has a parent identifier. Rather, determining the number 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

:
() .

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

---

(?), JOIN, . , , , , , .

(, _ child_count), .

.

Answer 1

, :

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

:

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