Percentage Query Help

[Serversphere]

This one has me really stumped. I am trying to grab winning percentages for players on a sports based website and I just can't get my head around the proper query. I have two tables:

Table 1: players
player_id
firstname
lastname

Table 2: game_data
id
player_id
game_id
season
position

I want to grab a player's id, name, games played, games won and winning percentage, which I know to be wins divided by total games played times 100. Total games played would be a count(*) and wins would be a count(*) where position is equal to 1. This will be done in a PHP function, so I also need the query to take into account season. There are other tables holding game and season information, but they are not relevent to this query.

So I've tried this query to grab season 1 winning percentages:

SELECT p.player_id,
CONCAT(p.firstname,' ',p.lastname) AS player_name,
COUNT(*) AS games_played,
(SELECT COUNT(*) FROM game_data WHERE player_id=p.player_id AND position=1 AND season=1) AS games_won,
(games_won/games_played)*100 AS winning_percentage

FROM game_data AS g

WHERE season=1

INNER JOIN players AS p
ON p.player_id=g.player_id

GROUP BY p.player_id

ORDER BY winning_percentage DESC

But it doesn't work. I get an error that games_won is an unknown field on the line that calculates the win percentage, so I am assuming I have to calculate this on the fly rather than post query. Any thoughts?

[Fumigator]

It looks good, but like the error says you can't use a derived column to create another column, so you'll need to re-state the subquery when calculating winning percentage.


SELECT p.player_id,
CONCAT(p.firstname,' ',p.lastname) AS player_name,
COUNT(*) AS games_played,
(SELECT COUNT(*) FROM game_data AS g1 WHERE g1.player_id=p.player_id AND g1.position=1 AND g1.season=1) AS games_won,
((SELECT COUNT(*) FROM game_data AS g2 WHERE g2.player_id=p.player_id AND g2.position=1 AND g2.season=1) / COUNT(*)) * 100 AS winning_percentage

FROM game_data AS g

WHERE season=1

INNER JOIN players AS p
ON p.player_id=g.player_id

GROUP BY p.player_id

ORDER BY winning_percentage DESC

[Serversphere]

That did it, thank you. I actually cheated a bit and used SUM(position=1) to come up the number of first place wins, since the value was 1 each time. The ending query looked like this:


SELECT g.player_id,
SUM(g.position=1) AS wins,
COUNT(g.id) AS played,
FLOOR(100/COUNT(g.id)*SUM(g.position=1)) AS pct,
CONCAT(p.firstname,' ',p.lastname) AS name
FROM game_data AS g
INNER JOIN players AS p
ON p.player_id=g.player_id
WHERE g.season=1
GROUP BY g.player_id
ORDER BY pct DESC
LIMIT 10


Which gives me the top 10 winning percentages on the team for a specific season. Pretty impressive! And much quicker than my old method of cycling through and using PHP Arrays to sort the data. Thanks for the help - greatly appreciated.

[Fumigator]

Glad you got it working-- nice query! :thumbsup:

[Serversphere]

This problem just took on a new twist. This query was for a Poker league website that tracks game play for people in the league. However the top few people are those who played 1 or 2 games out of 30 and won just that game (giving 100%!). Some of the regulars are complaining that winning percentage should only be a factor if you played in, say around 5 games during the season.

But I have no idea now how to insert this into the query! Tried:

SELECT g.player_id,
SUM(g.position=1) AS wins,
COUNT(g.id) AS played,
FLOOR(100/COUNT(g.id)*SUM(g.position=1)) AS pct,
CONCAT(p.firstname,' ',p.lastname) AS name
FROM game_data AS g
INNER JOIN players AS p
ON p.player_id=g.player_id
WHERE g.season=1
AND played>5
GROUP BY g.player_id
ORDER BY pct DESC
LIMIT 10

but, of course, Mysql complains that 'played' isn't actually a column. Any ideas?

[Fumigator]

This is where "HAVING" should be used.


.
.
.
GROUP BY g.player_id
HAVING count(*) > 5

[Serversphere]

Fumigator, you rock. :)

Could have sworn I tried with HAVING, but I think I may have used HAVING COUNT(wins)>5..

Thanks! Worked a charm!