CODEWITHSUNDEEP
mysql
Gordon
Gordon

Reputation: 57

mysql ranking based on sum of values

having a mysql table with multiple records belonging many different users like this:

id score

1 , 50

1 , 75

1 , 40

1, 20

2 , 85

2 , 60

2 , 20

i need to get the rank of each id but after finding the sum of their score;

the rank should be the same if the total score for each player is the same.

this gives me the total for each player:

select id,sum(score) as total from table_scores group by id order by total desc;

is it posssible to find the sum like above and use it to rank the players in one query?

Upvotes: 1

Views: 3326

Answers (3)

ShatyUT
ShatyUT

Reputation: 1365

Something big missing from the accepted answer. The rank needs to be bumped after a tie. If you've got 2 tied for 3rd place, there is no 4th place.

The following query is an adjustment of the accepted SQL to account for this and reset the rank variable (@r in the query) to match the row value. You can avoid the extra addition in the CASE/WHEN but initializing @row to 1 instead of 0 but then the row value is off by 1 and my OCD won't let that stand even if row number is not valuable.

select
  id, total,
  CASE WHEN @l=total THEN @r ELSE @r:=@row + 1 END as rank,
  @l:=total,
  @row:=@row + 1
FROM (
  select
    id, sum(score) as total
  from
    table_scores
  group by
    id
  order by
    total desc
) totals, (SELECT @r:=0, @row:=0, @l:=NULL) rank;

Upvotes: 2

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

i find one more way to this problem... This one is based on JOIN clause

SET @rank = 0;

SELECT t1.id, t1.score, t2.rank 
       FROM (SELECT id, SUM(score) as score 
             FROM table_scores GROUP BY id ORDER BY score Desc) AS t1
INNER JOIN
      (SELECT x.score, @rank:=@rank + 1 as rank FROM 
              (SELECT DISTINCT(SUM(score)) AS score 
               FROM table_scores 
               GROUP BY id ORDER BY score DESC) AS x) AS t2
ON t1.score = t2.score

Here is SQL Fiddle: http://sqlfiddle.com/#!9/2dcfc/16

P.S. it's interesting to see there is more then one way to solve a problem...

Upvotes: -1

fthiella
fthiella

Reputation: 49089

You can rank rows using variables:

select
  id, total,
  CASE WHEN @l=total THEN @r ELSE @r:=@r+1 END as rank,
  @l:=total
FROM (
  select
    id, sum(score) as total
  from
    table_scores
  group by
    id
  order by
    total desc
) totals, (SELECT @r:=0, @l:=NULL) rank;

Please see it working here.

Upvotes: 1

Related Questions