Scoreboard Output from MySQL Help.

[johnslater]

I have a question, something i SHOULD be able to do but i seem to be having a lapse in capabilities right now.

 

Basically i have a MySQL database with a number of tables. One table called 'Users' and one called 'Scores'. Basically Scores are linked to Users by a UserID because a user may have multiple scores for different games and levels.

 

Basically i want to output an overall scoreboard which adds up all the scores for each users and then outputs them in a big scoreboard.

 

So for example

 

USER - SCORE

John - 20

Dave - 140

John - 70

John - 200

Dave - 200

 

Would output on the scoreboard as...

Dave - 340

John - 290

etc..

 

Thanks guys.

[ignace]

SELECT player, score
FROM (
  SELECT concat(users.first_name, ' ', users.last_name) AS player, sum(scores.score) AS score
  FROM users
  JOIN scores ON users.id = scores.user_id
  GROUP BY player
) AS scoreboard
ORDER BY score DESC

 

Not the most efficient way as MySQL will use a temporary table for the sorting, but it will point you in the right direction.

 

I wonder if the below would work:

 

SELECT concat(users.first_name, ' ', users.last_name) AS player, sum(scores.score) AS score
FROM users
JOIN scores ON users.id = scores.user_id
GROUP BY player
ORDER BY sum(scores.score) DESC

[johnslater]

Worked a treat. Thanks for the help.