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

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
:slight_smile:
:slight_smile:

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

image

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
;

Your SQL is short bitsmed

Nice

:+1::+1: