[SOLVED] Ratio With Count() and Group By?

[josborne]

I have spent the whole day trying to figure this out and have realized I need help.

 

First:

 

SELECT Version( ) 

5.0.67-community


CREATE TABLE `Results_tbl` (
`Race_ID` int(10) NOT NULL,
`Season` year(4) NOT NULL,
`Rac` varchar(3) NOT NULL default 'RAC',
`Rider_ID` int(5) NOT NULL,
`Position` int(2) default NULL,
`Points` int(2) NOT NULL default '0',
`Race_Time` time default NULL,
`MSecs` char(5) default NULL,
`DNS` binary(1) NOT NULL,
`DNF` binary(1) NOT NULL,
`Outcome` varchar(7) default 'FIN'
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

 

I am trying to find the ratio of DNF's by each Rider_ID. It is easy to find the ratio for a single rider using:

 

SELECT
(SELECT COUNT(DNF) FROM Results_tbl 
WHERE DNF=1 and Rider_ID = 1 )/(SELECT COUNT(DNF) FROM Results_tbl WHERE Rider_ID = 1 ) AS Ratio

 

However, what I am trying to do is get the ratio for all Rider_IDs as with a GROUP BY

 

As in:

 

Rider_ID 	Ratio
---------+------
1 	     |  .54
2 	     |  .55
3 	     |  .35
4 	     |  .15
5 	     |  .05
6 	     |  .54
7 	     |  .24

 

 

Any help would be greatly appreciated. I am finding my self breaking into tears at regular intervals as I try to figure this out.

 

[kickstart]

Hi

 

This thread dealt with a very similar issue:-

 

http://www.phpfreaks.com/forums/index.php/topic,251117.0.html

 

Not that happy with my final SQL, but it does work.

 

All the best

 

Keith

[josborne]

Awesome. Thank you, sir.