Djinnes - 1 year ago 61
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.

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;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download