Sql Nested group by

Question

Following query returns a number of people having the same name with gender = Male.

select  lookup_name.firstname,count(lookup_name.firstname)
from lookup_name
where gender='M'
group by firstname

similarly, the query below returns a number of people having the same name with gender = Female.

select  lookup_name.firstname,count(lookup_name.firstname)
from lookup_name
where gender='F'
group by firstname

I need to write a query which finds out the name and tell the gender (whether male or female) with the greater count. i.e higher probability of that name in the database is of being male or female?

Answer 1

SELECT firstname, Male, Female,
       case when Male=Female then 'indeterminate'
            when Male>Female then 'probably male'
            else 'probably female' end MostProbablySex
FROM (
    select firstname,
           SUM(case when gender='M' then 1 else 0 end) Male,
           SUM(case when gender='F' then 1 else 0 end) Female
    from lookup_name
    group by firstname
) X;

Or a single pass:

select firstname,
       CASE SIGN(2.0 * SUM(case when gender='M' then 1 else 0 end) / COUNT(*) - 1)
       WHEN -1 then 'probably female'
       WHEN 0 then 'indeterminate'
       WHEN 1 then 'probably male'
       END
from lookup_name
group by firstname;