Simple stats page ranking with MySQL

Question

For some reason this seems easy but I cannot figure it out. So I have a table called "contest_entries" and in it has columns "username" and "rating".

The table reads something like this:

Username | rating | Contest_id
John     | 3      |   4
Mike     | 1      |   4
Eric     | 3      |   5
Mike     | 1      |   6
John     | 2      |   7
Mike     | 1      |   8 
John     | 1      |   9

So for different contests people can place in different positions,

My question is how can I show how many contests a username has won, taken 2nd or 3rd (taking 1st place, aka rating=1, 2nd place being rating=2, 3rd place being rating=3) and listing all of the usernames as well, even if the username never took 1st, 2nd,3rd. And then finally rank them by who has the most wins (who has the most "rating=1").

So it would look like this:

Username   |  Ranking | 1st places | Second Places | Third Places
Mike       |     1    |    3       |    0          |    0
John       |     2    |    1       |    1          |    1
Eric       |     3    |    0       |    0          |    1

etc etc.

score 1 · Answer 1 · answered Jun 29 '13 at 18:53

1

SELECT x.username
     , COUNT(CASE WHEN rating = 1 THEN 'foo' END) first 
     , COUNT(CASE WHEN rating = 2 THEN 'foo' END) second 
     , COUNT(CASE WHEN rating = 3 THEN 'foo' END) third
  FROM ratings x 
 GROUP 
    BY username;

answered Jun 29 '13 at 18:53

Strawberry

33,750
13
40
57

alright thats on the right track! (i changed the table name obv) but how can I list the rest of the usernames even tho they dont place in the top 3? Also How would I order them based on the number of wins! this is helping a lot tho so far! – Mike Kochendorfer Jun 29 '13 at 18:57

score 0 · Accepted Answer · answered Jun 29 '13 at 18:56

Try

SELECT username, @n := @n + 1 ranking, `1st places`, `2nd places`, `3rd places`
  FROM
(
  SELECT username,
  SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) `1st places`,
  SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) `2nd places`,
  SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) `3rd places`
    FROM Table1
   GROUP BY username
   ORDER BY `1st places` DESC
) q, (SELECT @n := 0) n

Output:

| USERNAME | RANKING | 1ST PLACES | 2ND PLACES | 3RD PLACES |
-------------------------------------------------------------
|     Mike |       1 |          3 |          0 |          0 |
|     John |       2 |          1 |          1 |          1 |
|     Eric |       3 |          0 |          0 |          1 |

Here is SQLFiddle demo

Simple stats page ranking with MySQL

2 Answers2