title: Calculate the number of direct children in a hierarchy
body: <p>I have a simple hierarchy of categories in an SQLite3 database, each row storing a parent ID or <code>NULL</code> as appropriate.</p>
  
  <p>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.</p>
  
  <hr>
  
  <p><strong>Table definition:</strong></p>
  
  <pre><code>CREATE TABLE category (
      id INTEGER PRIMARY KEY AUTOINCREMENT
      name TEXT NOT NULL
      parent_id INTEGER DEFAULT NULL
  );
  </code></pre>
  
  <p><strong>Sample data:</strong></p>
  
  <pre>
  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
  </pre>
  
  <p><strong>Desired output:</strong><br>
  The original data plus a count of how many subcategories (children) each row has.</p>
  
  <pre>
  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
  </pre>
  
  <hr>
  
  <p>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.</p>
  
  <p>Changing the table schema is a possibility (e.g. if a child_id or child_count) would be necessary, but I'd rather not.</p>
  
  <p>Any input would most appreciated.</p>