How do I calculate ratios in SQL?

Question

Suppose I have a GAME table with the following fields

user_id, result

I wish to calculate win percentage defined as total number of records where

result = 'Win'

Compared to total number of records where

result <> ''

How can I do this in SQL and have the result returned in order with highest win percentage first?

Answer 1

Most database systems should be able to handle this with something akin to:

Select user_id
    , Sum( Case When Result = 'Win' Then 1 Else 0 End ) / Count(*) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc

Btw, I'm assuming you meant to say the win percentage is the total number of wins out of the total number of games. If you really did mean where result <> '', then you would need to modify the query like so:

Select user_id
    , Sum( Case When Result = 'Win' Then 1 Else 0 End ) 
        / Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc

Addition

It was suggested in comments that this is SQL Server specific. It is not. Everything in the above solutions is part of the SQL standard. Whether a given database system implements all of the features required is obviously another matter. So, if we are switching gears to finding a solution that would actually work on most database systems, the one feature that has the most variance in support would likely be the use of the column alias in the Order By. You can achieve the equivalent in a couple of ways but one way that would work on most database systems would be to use a derived table:

Select Z.user_id, Z.WinPercent
From    (
        Select user_id
            , Sum( Case When Result = 'Win' Then 1 Else 0 End ) 
                / Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
        From Game
        Group By user_id
        ) As Z
Order By Z.WinPercent Desc

Answer 2

In MySQL, you can use this shortcut:

SELECT user_id, avg(Result = 'Win') AS WinPercent
FROM Game
GROUP BY user_id
ORDER BY WinPercent DESC

Assuming Result is never NULL (empty string is ok). Otherwise you have to use Result IS NOT NULL AND Result = 'Win'