Djinnes Djinnes - 24 days ago 6
SQL Question

The percentage of occurrences of a field per ID

I am making a website for a game as a hobby and I want to add a win percentage for every champion in it. I track every match in my DB and the outcome column can be 0 or 1 for loss or win. I am using MySql. I want my outcome table to be like this:

ChampID | Wins | Loss | Percentage
12 1 1 50%
13 2 0 100%


My Matches table looks like this:

MatchID | ChampID | PlayerID | MatchOutcome
1 12 1231 1
2 12 1414 0
3 13 1341 1
4 13 1512 1


So the champion with ID 12 will have 50% win rate while 13 will have 100%.

I have been trying unions and joins but I couldn't get them right, this is the closest I have come.

SELECT
(SELECT COUNT(*) FROM Matches WHERE (MatchOutcome LIKE "0")) AS Loss,
(SELECT COUNT(*) FROM Matches WHERE (MatchOutcome LIKE "1")) AS Win,
(SELECT (Win/(Win+Loss))*100 ) AS Percentage


This method seems the most promising as I would also like to be able to sort further by PlayerID.

Thanks for your time

Answer

Just do some simply math to do this. No reason to get creative with joins and subqueries:

 SELECT sum(matchoutcome)/count(matchid) FROM Matches where PlayerID = 12;

Or, for each player in a list:

SELECT playerID, sum(matchoutcome)/count(matchid) FROM Matches GROUP BY playerID;

You can do your conversion to a percentage here too:

SELECT playerID, (sum(matchoutcome)/count(matchid))*100 FROM Matches GROUP BY playerID;

To get out Wins and Losses in separate columns:

SELECT playerID, 
    sum(matchOutcome) as Wins,
    Count(matchid) - sum(matchOutcome) as Losses,
    (sum(matchoutcome)/count(matchid))*100 
FROM Matches 
GROUP BY playerID;