CODEWITHSUNDEEP
mysqlsql
Jeffery Mills
Jeffery Mills

Reputation: 189

Get standings/ranking based on win percentage from match-ups table

I have a table of Players and table of MatchUps. The MatchUps table has a winner_id column and a loser_id column. Can I get a standings/ranking based on winning percentage in one query? It would be great if I could return something like this for each player.

PlayerName - winCount - lossCount - winPercentage

I'm a SQL lightweight so things I've tried haven't come close. I've basically just been able to get a wins count for each player and that's it.

Here's an example players table:

+----+-------------------------------------+
| id | name                                |
+----+-------------------------------------+
|  1 | Iron Man                            |
|  2 | Gaurdians Of The Galaxy             |
|  3 | Batman Begins                       |
|  4 | X-Men                               |
|  5 | Captain America: The First Avenger  |
|  6 | Superman II                         |
|  7 | Captain America: The Winter Soldier |
|  8 | X2: X-Men United                    |
|  9 | X-Men: First Class                  |
| 10 | Superman: The Movie                 |
| 11 | Batman                              |
| 12 | The Avengers                        |
| 13 | The Incredibles                     |
| 14 | The Dark Knight                     |
+----+-------------------------------------+

Here's a sample match-ups table:

+----+-----------+----------+
| id | winner_id | loser_id |
+----+-----------+----------+
|  1 |         5 |        6 |
|  2 |         2 |        9 |
|  3 |         1 |        5 |
|  4 |         1 |        6 |
|  5 |         4 |       13 |
|  6 |         1 |       13 |
|  7 |         1 |        2 |
|  8 |         1 |        9 |
|  9 |         3 |        8 |
| 10 |         2 |        8 |
| 11 |         1 |        8 |
| 12 |         1 |       12 |
| 13 |         2 |       10 |
| 14 |         1 |       10 |
| 15 |         2 |        4 |
| 16 |         1 |        4 |
| 17 |         2 |       13 |
| 18 |         3 |       11 |
| 19 |         2 |        3 |
| 20 |         1 |        3 |
+----+-----------+----------+

Upvotes: 0

Views: 63

Answers (2)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Please try this :

select id,name,
ifnull(t1.win_count,0) as win_count,
ifnull(t2.loss_count,0) as loss_count,
concat(round((ifnull(t1.win_count,0) / (ifnull(t2.loss_count,0)+ifnull(t1.win_count,0))) * 100,0),' %') as win_percentage
from player as tbl
left join (
select winner_id,count(*) as win_count from matchups group by winner_id
) as t1 on t1.winner_id = tbl.player_id
left join (
select loser_id,count(*) as loss_count from matchups group by loser_id
) as t2 on t2.loser_id = tbl.player_id

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

This is one way to do this:

Fiddle: http://sqlfiddle.com/#!9/731d6/1/0

select  p.name as playername,
        x.wincount,
        x.losscount,
        case when x.wincount = 0 then 0
             else x.wincount / x.total_games
             end as winpercentage
from    (   select  player_id,
                    sum(case when outcome = 'W' then 1 else 0 end) as wincount,
                    sum(case when outcome = 'L' then 1 else 0 end) as losscount,
                    count(*) as total_games
            from    (
                        select  winner_id as player_id,
                                'W' as outcome
                        from    matchups
                        union all
                        select  loser_id,
                                'L'
                        from    matchups
                    ) x
            group by player_id
        ) x
   join players p 
     on x.player_id = p.id

Upvotes: 1

Related Questions