Trouble SUMs in query

[teach314]

hi to all

I have a table as shown below. Every ID has ranks 1 to 4.

ID    rank    X    Y
-------------------------
24      1     4
24      2     0
24      3     3
24      4     6

25      1     0
25      2     0
25      3     8
25      4     2

26      1 etc...

Within each ID value, I want to UPDATE field Y so that...

when rank = 1, we SUM the X values having rank between 1 and 4,
when rank = 2, we SUM the X values having rank between 2 and 4,
when rank = 3, we SUM the X values having rank between 3 and 4,
when rank = 4, we SUM the X values having rank between 4 and 4,
​

The output should look like...

ID    rank    X    Y
-------------------------
24      1     4   13
24      2     0    9
24      3     3    9
24      4     6    6

25      1     0   10
25      2     0   10
25      3     8   10
25      4     2    2

26      1     etc...

Thanks in advance for any help.
Teach314

 

[teach314]

found a solution...

SELECT 
	t1.ID, t1.rank, t1.X, Sum(t2.X) AS Y
FROM 
	Tbl_TEST AS t1 
INNER JOIN 
	Tbl_TEST AS t2 
ON 
	(t1.ID = t2.ID) AND 
	(t1.rank <= t2.rank)
GROUP BY 
	t1.ID, t1.rank, t1.X;