How can I make a query to get a ratio?-CodePudding

SELECT 
    sex, SUM(total) AS total
FROM
    (SELECT 
         games, sex, COUNT(sex) AS total
     FROM 
         dbo.athlete_events$
     GROUP BY 
         sex, games
     -- ORDER BY 1 
    ) a
GROUP BY 
    sex

How can I make a query that will give me the ratio of Male to Female? In this example, the result should be 196594/74522, i.e. 2.64.

CodePudding user response：

You might try to build on the following:

select Sum(Iif(sex='M',1.0,0)) / Sum(Iif(sex='M',0,1.0))
from dbo.athlete_events$

Note the use of 1.0 otherwise you would just get integer division.

CodePudding user response：

You could try:

select
    sum(case when sex='F' then 1.0 else 0.0 end) / sum(case when sex='M' then 1.0 else 0.0 end)
from (... your subquery...)