CODEWITHSUNDEEP
mysql
user2588088
user2588088

Reputation: 47

Simple MySQL Issue

OK I have a column with a W or L to designate a win or loss. I want to count all the wins and losses, and get a win percentage.

SELECT banner_name, count(win_loss) as wins 
FROM drop_log 
where win_loss = 'W' 
group by banner_name

This gives me all the teams and how many wins they have. How would I tweak this to have another column for losses and another one for ratio. MySQL appears to have different syntax rules than SQL Server 2008.

Upvotes: 2

Views: 62

Answers (2)

Explosion Pills
Explosion Pills

Reputation: 191749

SELECT banner_name, SUM(IF(win_loss = "W", 1, 0)) AS wins,
    SUM(IF(win_loss = "L", 1, 0)) AS losses
FROM drop_log
GROUP BY banner_name

You may also be able to get the percentage in this way, but it's easy to divide after the selection too.

It would also be better to use a boolean (0 or 1) to designate a win or loss, but this would work in pretty much the same way.

EDIT: It would actually work to simply use SUM(win_loss = 'letter') as this will return 1 or 0 already.

Upvotes: 6

Barmar
Barmar

Reputation: 780909

This will get you all 3 values you want.

SELECT banner_name, wins, total-wins AS losses, wins/total AS win_ratio
FROM (select banner_name, SUM(win_losses = 'W') AS wins, COUNT(*) AS total
      FROM drop_log
      GROUP BY banner_name) w

Upvotes: 1

Related Questions