Hi,
I have put together the following query to aggregate BatAves. I also want the query to return balls, SR and BPercent without them being part of the aggregation. ie I just want the same results as in the sub-query.
Can anyone point out how to do it?
Thanks
Code:
SELECT sub.League, Avg(BatAve) AS LgBatAveFROM (SELECT Matches.venue, Matches.League, Round(Sum(runs)/(Count(batsmanId)-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(ScorecardBatting.[_4s])+Sum(ScorecardBatting.[_6s]))/Sum(ballsFaced)*100,2) AS BPercent
FROM Matches INNER JOIN ScorecardBatting ON Matches.matchId = ScorecardBatting.matchId
GROUP BY Matches.venue, Matches.League
) AS sub
GROUP BY sub.League;