With SQL, how to calculate what percentage of rows has a specific value?

Question

I use PHP / MySQL to create a website that displays race results. I want to display statistics of the 10 most difficult races, i.e. The races that most people use DNF. I do 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 this does not take into account the number of people in the race. How do I modify the query to also return percentDNF (dnfs / totalracers) and arrange this column? Place = 0 means DNF.

Answer 1

Modify the sentence WHEREand use the operator CASEinstead to calculate the did-not-finish value. Then divide it into all riders. Something like that:

  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

Answer 2

, mysql, sql- : select (( count (*) tbl, ) * 100)/( count (*) tbl)