create table test_results
(
tot_pos float
,fin_pos int
,odds float
,course nvarchar(255)
)
insert into test_results
values (1,1,3.5,'wolves')
,(3,2,4.0,'wolves')
,(1,3,5.25,'wolves')
,(4,1,1.75,'wolves')
,(3,1,2.1,'wolves')
,(2,6,8.5,'wolves')
,(1,4,6.75,'wolves')
,(2,5,3.9,'wolves')
,(9,2,2.1,'wolves')
,(1,1,2,'wolves')
,(3,9,17.5,'wolves')
Using the above I am trying to produce an A/E stat based on the 'tot_pos' column. Where ever tot_pos =1 then those odds are converted to probability using this formula 1/odds. Those probability values are then added. A count is made of all the instances of tot_pos = 1 AND fin_pos=1. That value is then divided by the total probability value to give the AE stat. Put simply it goes thus
- where tot_pos =1 convert those odds to probability
2)add those values
3)count where tot_pos= AND fin_pos =1
4 divide the result from 3) by the result from 2)
Can it be done? Well certainly not by me, but hopefully someone can show me how to do it. Many thanks for your interest.