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

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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