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.
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