How can I add a Count with a condition from another table to a column/equation?

I currently have a table that calculates some player stats

SELECT COUNT(*) as 'Total Games',
        COUNT(case Position when 1 then 1 else null end) as 'Wins',
        CAST(CAST(COUNT(case Position when 1 then 1 else null end) as decimal(10,2)) / cast(Count(*) as decimal(10,2)) as decimal(10,2)) as 'Win %',        
        COUNT(case when Position IN(1,2) then 1 else null end) as 'Total HU',
        CAST(CAST(COUNT(case Position when 1 then 1 else null end) as decimal) / CAST(COUNT(case when Position IN(1,2) then 1 else null end) as decimal) as decimal(10,2)) as 'Win HU %',
        COUNT(case when Position IN(1,2,3,4,5,6,7,8) then 1 else null end) as 'Final Tables',
        CAST(CAST(COUNT(case Position when 1 then 1 else null end) as decimal) / CAST(COUNT(case when Position IN(1,2,3,4,5,6,7,8) then 1 else null end) as decimal) as decimal(10,2)) as 'Win % Final Tables' 
FROM EventResults
WHERE PlayerID = 1

Results

I am looking to also add the stat “Overall average % of field beaten”, but I am not sure how. The relevant columns in “EventResults” table are ‘EventID’, ‘PlayerID’ and ‘Position’

Image of EventResult table

The equation would be

(AVG( (Position of PlayerID with EventID) / (Count of that EventID) ) / -1 ) * -1

But I can’t wrap my head around how to get that into a column.

Answer

As I mentioned in the comments, you need to use analytical function to figure this out correctly. This query should calculate the average percent of field beaten for each player:

SELECT sub.playerid, AVG(sub.r_field_beaten) * 100 AS PCT_FIELD_BEATEN
FROM (SELECT er.playerid, 
             er.eventid, 
             (COUNT(*) OVER (PARTITION BY er.eventid)-er.position+1) / COUNT(*) OVER (PARTITION BY er.eventid) AS R_FIELD_BEATEN
      FROM EventResults er) sub
GROUP BY sub.playerid;

As you may have noticed, I changed your formula a bit. I took the total number of participants subtracted by the position plus 1 (The plus 1 is to take the player out of the field) and divided it by the total number of participants. Lets say you win a tournament of 100 people the formula would be (100-1+1)/100 resulting in 1. Thus you beat 100% of the field.