CODEWITHSUNDEEP
mysqlsqlinnodb
David
David

Reputation: 1613

Sum the highest scores per user and track

This is probably a easy one, but for the life of me I can't seem to figure it out. This is my table:

uid  |  userID  |  trackID  |  carID  |  highscoreDate  |  highscore
-----------------------------------------------------------------
 1        1            1         1           [date]         123
 2        1            1         1           [date]          44
 3        2            2         1           [date]         222
 4        2            1         1           [date]          28
 5        1            2         1           [date]          17

I would like to get the SUM of the highest highscores for each user and track. In the data above that would give:

 user 1: 140
 user 2: 250

Upvotes: 1

Views: 59

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

How about using a subselect first.

Something like

SELECT userID, SUM(highscore)
FROM (
    SELECT userID, trackID, MAX(highscore) highscore
    FROM MyTable
    GROUP BY userID, trackID
  ) s
GROUP BY userID

SQL Fiddle DEMO

Upvotes: 5

Related Questions