CODEWITHSUNDEEP
mysql
nebulousGirl
nebulousGirl

Reputation: 1364

MySQL ranking with GROUP BY and SUM

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!

Upvotes: 2

Views: 8983

Answers (6)

Tauseef
Tauseef

Reputation: 2052

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

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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.

Upvotes: 7

Alma Do
Alma Do

Reputation: 37365

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.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

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

Upvotes: 0

AdrianBR
AdrianBR

Reputation: 2588

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

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 3

Related Questions