title: How to calculate the sum of values in a tree using SQL
body: <p>I need to sum points on each level earned by a tree of users. Level 1 is the sum of users' points of the users 1 level below the user. Level 2 is the Level 1 points of the users 2 levels below the user, etc...</p>
  
  <p>The calculation happens once a month on a non production server, no worries about performance.</p>
  
  <p>What would the SQL look like to do it?</p>
  
  <p>If you're confused, don't worry, I am as well!</p>
  
  <p>User table:</p>
  
  <pre><code>ID    ParentID    Points
  1     0           230
  2     1           150
  3     0           80
  4     1           110
  5     4           54
  6     4           342
  
  Tree:
  0
  |---\
  1    3
  | \
  2  4---
      \  \
       5  6
  </code></pre>
  
  <p>Output should be:</p>
  
  <pre><code>ID    Points    Level1     Level2
  1     230       150+110    150+110+54+342
  2     150
  3     80
  4     110       54+342
  5     54
  6     342
  </code></pre>
  
  <p>SQL Server Syntax and functions preferably...</p>