CODEWITHSUNDEEP
sqljoin
qoeilufjtiefnceic
qoeilufjtiefnceic

Reputation: 13

SQL calculating percentage with a derived(non-existent) column using two tables

I'm using a chess dataset that uses two tables to calculate the percentage of players from each country with a FIDE of over 1800. One table already has a column with the total players in each country. So if I use this query:

SELECT Country, Total_Players
FROM international_ranking;

It'll list every country with the corresponding total number of players.

The other table is just a collection of player names(with a corresponding FIDE and country). So when I use this query:

SELECT Country, count(FIDE) 
FROM chess_players
WHERE FIDE >= 1800
GROUP BY Country;

It'll give me each country with the count of players having a score of atleast 1800.

Now, how do I combine these statements to calculate the percentage(count(FIDE)/Total_Players) for each country?

Upvotes: 1

Views: 84

Answers (3)

Isolated
Isolated

Reputation: 6454

You could use a CTE, which is an alternative to a join subquery.

with total_by_country as (
 SELECT Country, Total_Players
   FROM international_ranking
)
SELECT p.Country, count(p.FIDE) / t.total_players
  FROM chess_players p
  JOIN total_by_country t
    ON p.country = t.country
 WHERE p.FIDE >= 1800
 GROUP BY p.Country

Upvotes: 0

nbk
nbk

Reputation: 49395

You can join both Queries.

This is a inner join, so you wpould only receive countries that are in both tables

SELECT
    c1.Country,(count_fide/Total_Players) as perecntage
FROM
   international_ranking c1
JOIN 
(SELECT Country, count(FIDE) count_fide
FROM chess_players
WHERE FIDE >= 1800
GROUP BY Country) c2 ON c1.Country = c2.Country

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

You can join the tables to compute both counts:

select count(p.country) / count(distinct r.country) as percentage
from international_ranking r
left join chess_players p on p.country = r.country and p.fide >= 1800

Upvotes: 0

Related Questions