-2

I am trying to retrieve the country with the second highest percentage of female workers from my dataset.

Theses are the dimensions that are relevant from the table:

Table name is "employee" country(varchar) male(boolean) female(boolean)

Ryan Dennehy
  • 33
  • 5

2 Answers2

0
SELECT Country
FROM employee
GROUP BY Country -- get aggregate per Country
ORDER BY SUM(Female) / SUM(Male) DESC -- Higher percentage goes up
LIMIT 1, 1 -- after 1st, next 1 row(s)
;
Nae
  • 14,209
  • 7
  • 52
  • 79
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Adrian Mole Oct 27 '20 at 22:45
0

It depends on the database. Assuming you're using MySQL (most used DB) boolean is an equivalent to tinyint(1). This makes integer arithmetics without casting or if then else logic possible:

SELECT country, (sum(male)/ sum(female)) as ratio FROM employee GROUP BY country order by ratio desc limit 2;

Then pick 2nd result (or use Limit 1, 1 if you're really not interested in the highest value).

If you need to cast as you're using PostGres you can have a look here: postgresql - sql - count of 'true' values

supernova
  • 1,762
  • 1
  • 14
  • 31