Creating a SQL Query for Summation and Division

Question

I have this table:

Username | score | gameid
SimJoo   | 14    | 1
SimJoo   | 20    | 1
SimJoo   | 23    | 2
Master   | 32    | 1
Master   | 25    | 2
Master   | 9     | 2

For each player I need the sum of the highest player score on each playing field, divided by the highest score received for this gameplay. This is for any number of different players, scores and games.

The result of the above example table will be:

Username | total score
Master   | 2.000
SimJoo   | 1.545

For clarity:

• Calculation for the main player (32/32) + (25/25) = 2.000
• The calculation of the SimJoo player is (20/32) + (23/25) = 1,545

---

SELECT Username, SUM((SELECT MAX(score) WHERE Username=? AND gameid=?)/(SELECT MAX(Score) WHERE gameid=?)) AS total_score
GROUP BY Username
ORDER BY total_score DESC

Answer 1

The first subquery returns the user's maximum score per game. The second is max for games

select Username, sum(umax/gmax) 
    from 
      (select Username, gameid, max(score) umax 
          from t
        group by Username, gameid 
     ) u
    left join 
      (select gameid, max(score) gmax 
          from t 
        group by gameid
      ) g
    on u.gameid = g.gameid
  group by Username

example in sqlfiddle

Answer 2

From what you describe, it requires two aggregations and a union:

select t.username, sum(t.score / g.maxscore)
from t join
     (select gameid, max(score) as maxscore
      from t
      group by gameid
     ) g
     on t.gameid = g.gameid
group by t.username;