Getting win % from a table that holds "Winners" and "Losers"

user_1289841 0
Viewed 77

I am working with an SQLite database where I store matches/fights between players like so:

matchId[int] winner[text] loser[text]

I have made queries that sum up how many times a player has won a fight and another one for how many fights a player has lost. But is there a way, in SQL, to type this so that I can find the win% directly from the database or do I have to calculate that elsewhere? There is no problem calculating this elsewhere, but I got intrigued to figure out if/how it can be done purely in SQL.

What I am trying to achieve is basically:

SELECT winner, COUNT(winner) as Wins FROM Fights GROUP BY winner

divided by

SELECT loser, COUNT(loser) as Losses FROM Fights GROUP BY loser;

for each player, which in this table is either a "winner" or a "loser". I also have a table (Players) that holds all these players as "player" that could be utilized to make this work.

union select group-by sqlite sql
1 Answers

You can use union all and aggregation:

select player, avg(win) as win_ratio
from (
    select winner as player, 1.0 as win from fights
    union all
    select loser, 0 from fights
) t
group by player

This gives you, for each player that participated at least one fight, a decimal number between 0 and 1 that represents the win ratio.

user_106767160
Related