TABLE1
ID CLASS SCORE
1 A 2
2 A 13
3 A 27
4 A 23
5 A 15
6 A 16
7 A 19
8 A 23
9 A 17
5 B 1
9 B 18
15 B 7
16 B 26
17 B 26
18 B 21
19 B 22
I have TABLE1 and wish to make this
CLASS ID SCORE TOTSCORE3 TOTSCOREALL
A 3 27 73 155
A 4 23 73 155
A 8 23 73 155
B 16 26 74 121
B 17 26 74 121
B 19 22 74 121
with THESE RULES, for every CLASS find the THREE IDS with HIGHEST SCORE, then add up those values for TOTSCORE3 and then add up ALL values for a CLASS for TOTSCOREALL
my ATTEMPT:
SELECT CLASS, ID, SCORE, RANK() OVER(ORDER BY SCORE DESC) AS RANK, SUM(SCORE) AS TOTSCOREALL
FROM TABLE1
GROUP BY CLASS
WHERE RANK <=3
