Sql Nested group by
SELECT firstname, Male, Female, case when Male=Female then 'indeterminate' when Male>Female then 'probably male' else 'probably female' end MostProbablySexFROM ( 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' ENDfrom lookup_namegroup by firstname;