'How to get a ratio of male to female medal winners where a single name can show up multiple times?

Say I have the following table:

Name Sex Medal
John M Gold
John M Silver
Chris M Bronze
Ana F Null
Isobel F Bronze

I would like to get the ratio of Male to Female medal winners; in this case, I need to get the number 2 (John and Chris won medals, And won a medal). I don't know how to do this.

What I have is simply listing the number of distinct medal winners, grouped by gender:

SELECT "Sex", COUNT( DISTINCT "Name" ) AS number_of_medal_winners
FROM table
WHERE "Medal" IS NOT NULL
GROUP BY "Sex";

which results in

Sex number_of_medal_winners
F 1
M 2
postgresql


Solution 1:[1]

Use a case expression to filter what is counted to build your ratio:

SELECT COUNT(CASE WHEN sex = "M" THEN name END)/COUNT(CASE WHEN sex = 'F' THEN name END) as ratio_of_male_medal_winners_to_female
FROM yourtable
WHERE Medal IS NOT NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JNevill

Related Questions