How to calculate the sum of values in a tree using SQL

Question

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...

The calculation happens once a month on a non production server, no worries about performance.

What would the SQL look like to do it?

If you're confused, don't worry, I am as well!

User table:

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

Output should be:

ID    Points    Level1     Level2
1     230       150+110    150+110+54+342
2     150
3     80
4     110       54+342
5     54
6     342

SQL Server Syntax and functions preferably...

Answer 1

If you were using Oracle DBMS that would be pretty straightforward since Oracle supports tree queries with the CONNECT BY/STARTS WITH syntax. For SQL Server I think you might find Common Table Expressions useful

Answer 2

Trees don't work well with SQL. If you have very (very very) few write accesses, you could change the tree implementation to use nested sets, that would make this query incredibly easy.

Example (if I'm not mistaken):

SELECT SUM(points) 
FROM users 
where left > x and right < y 

However, any changes on the tree require touching a massive amount of rows. It's probably better to just do the recursion in you client.