CODEWITHSUNDEEP
sql-servert-sqlgroup-bypercentage
Roger Dodger
Roger Dodger

Reputation: 987

How to calculate percentages in tsql using a group by query?

I have a table that lists results by sport and start and end dates and win/loss result. I need to calculate the percentage of wins per sport per season_start. This is my table:

table1
******
id    sport  won   playerid     season_start  season_end    updated_date
---   -----  ----  --------     ------------ ------------  ------------
1     rugby   Y    kato23       2016-01-01     2016-01-31   2016-02-01
2     rugby   Y    king54       2016-01-01     2016-01-31   2016-02-01
3     rugby   N    robby1       2016-03-01     2016-03-28   2016-04-01
4     rugby   Y    kelly2       2016-03-01     2016-03-28   2016-04-01
5     soccer  Y    kato23       2016-01-01     2016-01-31   2016-02-01
6     soccer  Y    jeri44       2016-01-01     2016-01-31   2016-02-01
7     soccer  N    matt24       2016-06-01     2016-06-30   2016-07-01
8     tennis  Y    kray43       2016-01-01     2016-01-31   2016-02-01
9     tennis  Y    jeri44       2016-01-01     2016-01-31   2016-02-01
10    tennis  N    jeri44       2016-01-01     2016-01-31   2016-02-01

So I'd like to run a query that produces the following result:

sport     season_start     success_percent
-----     ------------     ---------------
rugby     2016-01-01        100.00
rugby     2016-03-01        50.00
soccer    2016-01-01        100.00
soccer    2016-06-01        0.00
tennis    2016-01-01        66.66

I tried :

select sport,won, season_start, count(sport) as sportcount
from table1 where won = 'Y'
group by sport, won, season_start

But this just give me the entire count but not the actual success percent. I tried using case statement but I'm not too good with tsql, or with using 'Sum' to tally the wins and the losses and then calculate the win percentage out of the total.

Can someone provide a tsql query that does this?

Upvotes: 2

Views: 1770

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

Please try the following query:

SELECT sport, season_start,
       (CAST(COUNT(CASE won WHEN 'Y' THEN '1' ELSE NULL END) AS DECIMAL(10,2))) / CAST(COUNT(*) AS DECIMAL(10,2)))*100 AS sportcount
 FROM table1
 GROUP BY sport, season_start

In fact inside the COUNT aggregate function we check the value of won column and if it is equal to 'Y' we will count it otherwise we will ignore it. As you may know Count aggregate function ignore the NULL values, so inside the Count if won is not equal to 'Y' we produce a NULL value that will make the Count to ignore it. After we Count the won counts, we simply need to divide it to total matches count per sport and season(Count(*) will count this).

You have placed won in group by, that will cause problem. Each records with same Groups will be presented in result set just once. If you need to have won in your select list you may try the CROSS APPLY or Window Aggregate Functions. If you need so, please update your question and after that I will update my answer based on it.

Please take note that you may need to CAST or CONVERT the percentage calculation part to decimal or numeric or etc data type if you will need to have the precise result.(This is why I have cast the both Counts to DECIMAL)

Upvotes: 1

sstan
sstan

Reputation: 36533

You can use conditional counts for this:

select sport, season_start,
       sportcount = count(case when won = 'Y' then 'X' end) * 100.0 / count(*)
  from table1
 group by sport, season_start

Upvotes: 1

Related Questions