johnslater Posted January 22, 2011 Share Posted January 22, 2011 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. Link to comment https://forums.phpfreaks.com/topic/225320-scoreboard-output-from-mysql-help/ Share on other sites More sharing options...
ignace Posted January 22, 2011 Share Posted January 22, 2011 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 Link to comment https://forums.phpfreaks.com/topic/225320-scoreboard-output-from-mysql-help/#findComment-1163593 Share on other sites More sharing options...
johnslater Posted January 23, 2011 Author Share Posted January 23, 2011 Worked a treat. Thanks for the help. Link to comment https://forums.phpfreaks.com/topic/225320-scoreboard-output-from-mysql-help/#findComment-1163942 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.