MySQL ranking with GROUP BY and SUM

Question

I have a point table with some columns being:

| user_id | points |
--------------------
|   1     |   10   |
|   5     |   10   |
|   5     |   50   |
|   3     |   15   |
|   3     |   10   |

I would like to get the rank for each user with MySQL.

I've seen this post MySQL get row position in ORDER BY but it doesn't have a SUM in it and I can't get it to work with SUM.

I would like to be able to get the rank of a given user_id.

Thanks in advance for your help!

Answer 1

Reminding the OP's question:

I would like to be able to get the rank of a given user_id.

In order to actually perform operations over the @rank you have to user another derived table (yes, it is inefficient, that's why it is better not to handle this in MySQL):

SELECT * FROM (
  SELECT s.*, @rank := @rank + 1 rank FROM (
    SELECT user_id, sum(points) TotalPoints FROM t
    GROUP BY user_id
  ) s, (SELECT @rank := 0) init
  ORDER BY TotalPoints DESC
) r
WHERE user_id = 3

Output

| USER_ID | TOTALPOINTS | RANK |
|---------|-------------|------|
|       3 |          25 |    2 |

The process is basically:

1. Get the total amounts of points per user
2. Sort by those total points ranking
3. Filter once you have the rank (otherwise, the rank will be compromised)

Fiddle here.

Answer 2

Try this::

SELECT @rownum:=@rownum + 1 as row_number, 
       t.*
FROM ( 

select user_id, SUM(points) as Addedpoint
from mytable group by user_id order by Addedpoint desc

) t,
(SELECT @rownum := 0) r

Answer 3

You can achieve that with subquery, inside which you should calculate your sum:

SELECT 
  @rank:=@rank+1 AS rank, 
  user_id, 
  total_points 
FROM 
  (SELECT 
    user_id, 
    SUM(points) AS total_points 
  FROM t 
  GROUP BY 
    user_id) AS sum_points 
CROSS JOIN 
  (SELECT @rank:=0) AS init 
ORDER BY 
  sum_points.total_points DESC

-see my fiddle.

Answer 4

select 
@rownum := @rownum + 1 AS position, 
user_id, 
total_points

from
(select user_id, sum(points) as total_points from table)a 
join 
(SELECT @rownum := 0) r
order by total_points desc

Answer 5

Using a user variable you can do something like this:-

SELECT user_id, tot_points, @Rank:=@Rank + 1 AS user_rank
FROM
(
    SELECT user_id, SUM(points) AS tot_points
    FROM SomeTable
    GROUP BY user_id
    ORDER BY tot_points DESC
) Sub1
CROSS JOIN (SELECT @Rank:=0) Sub2

Answer 6

http://sqlfiddle.com/#!2/d0be9/1

SET @rank=0;
select @rank:=@rank+1 AS rank, pointsScored.user_id, sumPoints
from (
   select user_id , SUM(points)as sumPoints
   from point
   group by user_id 
   order by  sumPoints desc
)as pointsScored