Hi i am tryin to get the max of collection of calculated averages but when i try to use the simple option of using the max outside the avg function i get this error "Server: Msg 130, Level 15, State 1, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery." so im just wondering if anyone has any idea how to avoid this problem this is my code
Select 'Team Averages ' = max(avg(case Code when 'A' then 4.0 when 'B' then 3.0 when 'C' then 2.0 when 'D' then 1.0 else 0.0 end)) From ScoutingReport sr Where sr.Type = 1 And sr.ScoutId = 11619 group by matchid
and this is my output at the moment there are 3 teams and the averages are ----------------------------- Team Averages 1.812500 2.937500 1.187500 ----------------------------- thanks in advance Tim
Select max(average) as 'Team Averages ' from
(
Select avg(case Code when 'A' then 4.0 when 'B' then 3.0 when 'C' then 2.0 when 'D' then 1.0 else 0.0 end) average
From ScoutingReport sr
Where sr.Type = 1
And sr.ScoutId = 11619
group by matchid
) T
Cannot perform an aggregate function on an express
Forum Locked
Posted - 06/14/2006 : 09:56:35
