title: Recursive sum in tree structure
body: <p>I have a tree struture in a single table. The table is a tree of categories that can be nested endlessly. Each category has a ProductCount column that tells how many products are directly in the category (not summing child categories).</p>
  
  <pre><code>Id  | ParentId | Name      | ProductCount
  ------------------------------------
  1   | -1       | Cars      | 0
  2   | -1       | Bikes     | 1
  3   | 1        | Ford      | 10
  4   | 3        | Mustang   | 7
  5   | 3        | Focus     | 4
  </code></pre>
  
  <p>I would like to make a sql query that for each row/category gives me the number of products including the ones in the child categories.</p>
  
  <p>The output for the table above should be</p>
  
  <pre><code>Id  | ParentId | Name      | ProductCount | ProductCountIncludingChildren
  --------------------------------------------------------------------------
  1   | -1       | Cars      | 0            | 21
  2   | -1       | Bikes     | 1            | 1
  3   | 1        | Ford      | 10           | 21
  4   | 3        | Mustang   | 7            | 7
  5   | 3        | Focus     | 4            | 4
  </code></pre>
  
  <p>I know I probably should use CTE, but cant quite get it working the way it should.</p>
  
  <p>Any help is appreciated!</p>