title: Counting number of children in hierarchical SQL data
body: <p>for a simple data structure such as so:</p>
  
  <pre><code>ID    parentID    Text        Price
  1                 Root
  2     1           Flowers
  3     1           Electro
  4     2           Rose        10
  5     2           Violet      5
  6     4           Red Rose    12
  7     3           Television  100
  8     3           Radio       70
  9     8           Webradio    90
  </code></pre>
  
  <p>For reference, the hierarchy tree looks like this:</p>
  
  <pre><code>ID    Text        Price
  1     Root
  |2    Flowers
  |-4   Rose        10
  | |-6 Red Rose    12
  |-5   Violet      5
  |3    Electro
  |-7   Television  100
  |-8   Radio       70
    |-9 Webradio    90
  </code></pre>
  
  <p>I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so:</p>
  
  <pre><code>ID    parentID    Text        Price  NoOfChildren
  1                 Root               8
  2     1           Flowers            3
  3     1           Electro            3
  4     2           Rose        10     1
  5     2           Violet      5      0
  6     4           Red Rose    12     0
  7     3           Television  100    0
  8     3           Radio       70     1
  9     8           Webradio    90     0
  </code></pre>
  
  <p>I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here.</p>