You can calculate the overround on any race in Smartform with a one line query. Before getting to how the query works, it helps to break down how the overround for any race is calculated.
The overround is the sum of implied probabilities for all horses in a race - (ie. 1.0 or 100%, as you prefer). So to calculate the overround we need the implied probabilities for each horse in any given race.
Normally the implied probability of all horses winning a race should add up to 1, but of course the bookmakers require a profit margin for their services which means that the sum of probabilities will be above 1, and the difference is termed the bookmakers profit margin, overround, take, juice or vigorish "vig" (in the USA).
Where the starting price is in decimal format (as opposed to fractional format), the implied probability of the horse winning the race (at least according to the bookmakers' implied estimate) is 1/starting_price_decimal. Starting prices are shown in fractional and decimal format in Smartform. Here, we want to use the starting_price_decimal field.
You can use Smartform from the MySQL command prompt to return the implied probabilities for each contender as follows:
>select 1/starting_price_decimal AS 'implied prob', name, finish_position, unfinished, starting_price_decimal, starting_price from historic_races join historic_runners using (race_id) where race_id="291567" order by starting_price_decimal;
Here the race_id chosen is the Epsom Derby in 2010. You should replace this with a race_id, set of race_ids, or date range for all race_ids according to the races you want.
So far so good, this gives us the implied probability for each horse winning the race according to its starting price. So we can see that the favourite, Jan Vemeer, accounted for over 30% of the chances in the book.
But what about the overround for the whole race? Now we have the method for one horse, the rest is easy. All we need to do is sum all of the implied probabilities (by applying the SUM function to all the implied probabilities in the race). We don't need any of the other details, so our query is as follows:
>select SUM(1/starting_price_decimal) AS 'implied_probs' from historic_races join historic_runners using (race_id) where race_id="291567";
You should get the result:
+-------------------+
| implied_probs |
+-------------------+
| 1.177892 |
+-------------------+
1 row in set (0.00 sec)
Although it should be clear, for completeness, we can take off the 1 to find the exact take as a decimal value (or multiply by 100 if requiring it as a percentage). Also, since we are returning no other fields (and therefore do not require a join operation), we can shorten the query statement by getting rid of the search on the historic_races table:
mysql> select SUM(1/starting_price_decimal)-1 AS 'overround' from historic_runners where race_id="291567";
+--------------+
| overround |
+--------------+
| 0.177892 |
+--------------+
1 row in set (0.00 sec)
This last statement is all you really need to solve this one, replacing any race_id that you want the overround for with the race_id of choice.
