Hi,
I have put together the query below to perform various calculations. It works ok but I am trying to now calculate the average of the BatAve results, ie the average of all BatAves in League.
I thought
would work but I just get a "Subqueries cannot be used in the expression" error.
Can anyone see what the issue is?
Thanks
I have put together the query below to perform various calculations. It works ok but I am trying to now calculate the average of the BatAve results, ie the average of all BatAves in League.
I thought
Code:
AVG([BatAve]) AS LgBatAve
Can anyone see what the issue is?
Thanks
Code:
SELECT Data.venue, Data.League, Round(Sum(runs)/Sum(IIf([howDismissed]<>"not out",1,0)),2) AS BatAve, Sum(ballsFaced) AS balls, Round(Sum(runs)/Sum(ballsFaced)*100,2) AS SR, Round((Sum(Batting.[_4s])+Sum(Batting.[_6s]))/Sum(ballsFaced)*100,2) AS BPercent
FROM Data INNER JOIN Batting ON Data.matchId = Batting.matchId
GROUP BY Data.venue, Data.League;