CODEWITHSUNDEEP
sqlcountwhere-clause
Chan Alaster
Chan Alaster

Reputation: 23

How do I calculate the percent of the total per year when COUNT and WHERE is used?

In SQL Server, I have a query

SELECT season, COUNT(DISTINCT player_name) AS 'No. of Foreign Players'
FROM nbastats
WHERE country <> 'USA'
GROUP BY season

It return these results

id season No. of Foreign Players
1 1996-97 9
2 1997-98 14
3 1998-99 22
4 1999-00 24
5 2000-01 40
6 2001-02 51
7 2002-03 62

What I'm trying to do is to instead get the percentage of foreign players (over total players) each season. The database only provides "country" so I assume I can only use

WHERE country <> 'USA'

and perhaps divide the total but I am unsure how to with WHERE in the way. Any help would be greatly appreciated!

Upvotes: 2

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I think you want a ratio of a conditional:

SELECT season,
       COUNT(DISTINCT CASE WHEN country <> 'USA' THEN player_name END) * 1.0 / COUNT(DISTINCT player_name) AS foreign_ratio
FROM nbastats
GROUP BY season

Upvotes: 1

Related Questions