Prize money won to date is often touted as a better measure of class than any other attribute in horseracing, including the class of races won.

To be sure, it’s not a bad marker, and it’s one of the many attributes available in our programmatic database, Smartform.

But how to use this attribute?   Let’s start with total prize money won by each horse.

We can test this for the favourite in the Grand National – here, we want to take the sum of all historic prize_money, limited to the record over the race type in question, chases, as follows:

mysql> select SUM(prize_money) AS ‘total prizes’ from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);

1 row in set (2.15 sec)

Note the inclusion in the query statement of a subclause to exclude races where the horse in question has been a non-runner.  So far so good, but whilst this may tell us something about class over the horse’s whole career, it does not tell us much about its recent performances.  So let’s constrain the time period to the last year and a bit.

select SUM(prize_money) AS ‘total prizes’ from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”) ORDER BY historic_races.meeting_date DESC;

1 row in set (0.43 sec)

So we’re happy with the time period – and also that we can obtain total prize money for each contender.  But how to compare all the runners – is ranking them by their total a fair representation of ability?  What if some runners raced 10 times and other raced once?  Their average prize should enable a fair comparison, as follows:

>select AVG(prize_money) from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);

1 row in set (0.43 sec)

Let’s check back over the runs of Synchronised to sanity check this average:

> select finish_position, unfinished, prize_money from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”) ORDER BY historic_races.meeting_date DESC;

4 rows in set (0.41 sec)

There’s a mistake here.  MySQL assumes because the result of prize money is NULL for one of the races, when calculating the average, that the average is only calculated over 3 races and not 4.

We can get around this with a trick to treat NULL values in prize money as zeros, which is actually what we want here, so that the average is calculated correctly, as follows:

select AVG(COALESCE(prize_money,0)) from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);

1 row in set (0.42 sec)

If we do this for every runner, we can start to rank them. Typically, this type of operation is much easier using a programming language interfacing with Smartform – we show examples with Perl and R using Smartform elsewhere on the site – running the above query for every runner and saving results. An example output from such a program which calculates average prize money won, run over the Grand National contenders today, is as follows:

Key: Race time, course, form figures, name, average prize money, forecast price.

16:15, Aintree , 1/1121-4 , Ballabriggs ,    179611.33 , 13.00
16:15, Aintree , 3-P7311 , Synchronised , 93158.40 , 8.00
16:15, Aintree , 144P3/4- , State of Play , 50445.00 , 34.00
16:15, Aintree , 2-133F5 , Organisedconfusion, 32098.06, 21.00
16:15, Aintree , 6741UP , Alfa Beat , 21655.24 , 34.00
16:15, Aintree , 01/35-23 , Planet Of Sound , 21377.50 , 26.00
16:15, Aintree , 18P-13P , Weird Al , 19542.50 , 34.00
16:15, Aintree , 1/321-82 , Junior , 19298.00 , 11.00
16:15, Aintree , 11PB-P1 , West End Rocker , 14067.50 , 11.00
16:15, Aintree , 2F-8511 , Calgary Bay , 13634.50 , 26.00

All done with a Smartform and Perl script.

On the strength of class (remembering that prize money is the best indication of that), we would therefore expect the top few contenders above to be involved at the finish.