How to calculate coefficients in SQL?

Question

Suppose I have a table GAMEwith the following fields

user_id, result

I want to calculate the winning percentage, defined as the total number of entries, where

result = 'Win'

Compared to the total number of records, where

result <> ''

How can I do this in SQL and return the result in order of greatest gain?

Answer 1

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

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

By the way, I assume that you wanted to say that the percentage of winnings is the total number of wins from the total number of games. If you really mean it where result <> '', then you will need to modify the query as follows:

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

Adding

, SQL Server. . SQL. , , , . , , , , , , Order By. , , , :

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

MySQL :

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

, NULL ( ). = 'Win'