# Create CTE

``````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

1. where tot_pos =1 convert those odds to probability
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.

What would your expected result be, on the sample data you gave us?

result for 2)=1.12
result for 3)=2
result for 4)= 1.77

so the final figure is an AE of 1.77

hi

i tried to do this

hope it helps  drop create data ...
``````   drop table test_results
go

create table test_results
(
tot_pos float
,fin_pos int
,odds float
,course nvarchar(255)
)
go

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')
go
``````
SQL ..
``````;WITH cte
AS (SELECT a.tot_pos,
Sum(odds) AS sumodds
FROM   (SELECT *
FROM   test_results
WHERE  tot_pos = 1) a
GROUP  BY a.tot_pos),
cte2
AS (SELECT tot_pos,
Count(*) counttotfin1
FROM   test_results
WHERE  tot_pos = 1
AND fin_pos = 1
GROUP  BY tot_pos)
SELECT a.tot_pos,
a.sumodds / b.counttotfin1
FROM   cte a
JOIN cte2 b
ON a.tot_pos = b.tot_pos
`````` Fantastic! Thank you. Swapped the divisor and dividend around but exactly what I needed. Many thanks to all who showed an interest.

Alternative:

``````select course
,sum(case when fin_pos=1 then 1 else 0 end)
/sum(1/odds)
from test_results
where tot_pos=1
group by course
;
``````  