0

On my game ranking system, users can rate the games using three categories (eg: cat1, cat2, cat3).

table game_ranking
| id | user_id | game_id | cat1 | cat2 | cat3 |
| 1  | 1       | 1       | 5    | 7    | 8    |
| 2  | 1       | 2       | 10   | 8    | 5    |
| 3  | 2       | 2       | 1    | 4    | 5    |
| 4  | 3       | 1       | 5    | 7    | 8    |
| 5  | 4       | 1       | 2    | 3    | 6    |
| 6  | 7       | 3       | 6    | 6    | 3    |
| 7  | 9       | 3       | 3    | 10   | 7    |

The ranking must be based on the total of the sum of each category column.

Eg: game_id 2 score = cat1(10 + 1) + cat2(8 + 4) + cat3(5 + 5). So game_id 2 score is 33.

If two or more games have the same score, the game that has the highest number of votes should be on top of the other.

I need a MySql query to create the ranking based on these criterias I mentioned above.

Thanks!

bleroy
  • 311
  • 1
  • 2
  • 8
  • Have you tried any aggregating functions like SUM, or ordering using ORDER BY? Any errors or reason you can't use those well-documented methods? – Chris Baker May 29 '14 at 17:44

1 Answers1

0

A fairly straight forward SUM/GROUP BY will do what you need;

SELECT game_id, SUM(cat1+cat2+cat3) score, COUNT(*) votes
FROM game_ranking
GROUP BY game_id
ORDER BY score DESC, votes DESC

An SQLfiddle to test with.

The query sums up all categories and counts the number of votes, grouping by game_id. After that, it's just a simple matter or ordering by what you need.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294