Mssql how to select SUM with top 10 row

Question

My Data example is as below

UserName    Score     Subject
_____________________________
James       80        DDC1
James       90        EGG2
Amy         80        OPP8
Jemmy       50        CBC5
Linko       90        DD1
Bowie       80        AZZ6
Bowie       100       GGC1
Bowie       100       EOO2

I would like to select the top 3 highest total score , The Output should be as follow ,

UserName    Score     
__________________
Bowei       280
James       170
Linko       90        

May I know how to write this in Mssql query ?

Answer 1

Use a GROUP BY clause to group rows by UserName. The SUM() aggregate can be used to compute the total score per group (UserName). Use an ORDER BY clause to show the largest aggregate scores first. Lastly, TOP lets you select the first N results.

select top 3 UserName, sum(Score) as Score
from UserScores
group by UserName
order by sum(Score) desc