With SQL, how do I calculate what percentage of rows have a certain value?

Question

I'm using PHP/MySQL to make a website that displays race results. I want to display a statistic of the 10 hardest races ie the races that most people DNF. I'm doing it like this:

select raceid,race.name,race.location,race.date,count(result.raceid) as dnfs 
from result 
inner join race on result.raceid=race.id 
where result.place=0 
group by result.raceid 
order by dnfs desc limit 10

But that doesn't take in to account the number of people in the race. How would I modify the query to also return percentDNF (dnfs/totalracers) and order by that column? A place=0 means DNF.

Answer 1

Remove WHERE clause and use a CASE statement instead to calculate the did-not-finish value. Then divide that by total racers. Something like this:

  SELECT result.raceid, race.name, race.location, race.date, 
         COUNT(result.raceid) AS TOTAL, 
         SUM(CASE WHEN result.place = 0 THEN 1 ELSE 0 END) AS DNF, 
         SUM(CASE WHEN result.place = 0 THEN 1 ELSE 0 END) / 
             COUNT(result.raceid) AS PCT_DNF
    FROM result 
         JOIN race ON result.raceid=race.id 
GROUP BY result.raceid, race.name, race.location, race.date
ORDER BY SUM(CASE WHEN result.place = 0 THEN 1 ELSE 0 END) / 
             COUNT(result.raceid) DESC LIMIT 10