SQL Server dense_rank with sum

Question

I have a query,which is not returning proper result, i want my query to return total of score for same user_id, so that each user_id will have only one record with sum of all of its score.

My query is this:

SELECT
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
    user_id, 
    SUM(score) AS total_score
FROM 
    account_game
GROUP BY
    user_id, score
ORDER BY
    rank ASC

Query output is :

   rank user_id total_score
   1      2          4837
   2      1          600
   2      6          600
   3      1          30
   4      1          20

There should be three records with user_id 1,2,6

Expected result should be

  rank  user_id total_score
  -------------------------
   1     2      4837
   2     6      700
   3     1      650

Please suggest

Answer 1

As StuartLC commented, you can just remove the score from your GROUP BY and all should be fine:

SELECT DENSE_RANK() OVER (Order by SUM(score) DESC) AS rank,
user_id, 
SUM(score) as total_score
FROM 
account_game
GROUP BY user_id
ORDER BY rank ASC