CODEWITHSUNDEEP
mysql
Jo-Herman Haugholt
Jo-Herman Haugholt

Reputation: 462

Sum of x highest per row in mysql

I have a MYSQL table with the columns (userid, score). I want to group the userid's and get a column with the sum of the X highest scores for each userid.

E.g. For the sum of the two highest scores:

userid | score
-------+-------
01     | 1
01     | 1
01     | 2
02     | 1
02     | 2
02     | 3

to:

userid | scoresum
-------+----------
01     | 3
02     | 5

But I can't seem to figure out how to do this in MYSQL.

Upvotes: 1

Views: 309

Answers (3)

Gidon Wise
Gidon Wise

Reputation: 1916

this is a simple query that gives you all the data. now you just have to split the top_scores by /,/ and then add them up in your code. an alternative is a stored procedure but i think this is simpler. good luck.

select GROUP_CONCAT(score ORDER BY score DESC) top_scores
from userscore
group by userid

Upvotes: 0

Gidon Wise
Gidon Wise

Reputation: 1916

select
  userid,
(
select sum(highestscores)
from (
  select * 
  from userscore us2 
  where us2.userid = us1.userid 
  order by score desc limit 5
)
) as scoresum
from ( select distinct userid from userscore ) us1

so basically you need a sub query to get the 5 highest scores. you then sum those with another sub query. and you run that whole business for each unique user_id from your one and only table, userscore.

Upvotes: 3

K6t
K6t

Reputation: 1845

select SUM(MAX(col.name)) From table name Group by usedid

Upvotes: 0

Related Questions