Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Question

Post reply

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

tadhg88

Ten Centuries

Points: 1031
More actions
June 14, 2006 at 7:54 am

#103181

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JeffB Hall of Fame Points: 3541 More actions · Answer 1

Try using a subquery like the following:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport values(1, 11619, 1, 'B')

insert @ScoutingReport values(1, 11619, 1, 'D')

insert @ScoutingReport values(1, 11619, 2, 'C')

Select matchid, 'Team Averages ' = max(avgscore)

from (select matchid, 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) avgscore

From @ScoutingReport sr

Where sr.Type = 1

And sr.ScoutId = 11619

group by matchid) s

group by matchid

tadhg88 Ten Centuries Points: 1031 More actions · Answer 2

tadhg88

Ten Centuries

Points: 1031

June 14, 2006 at 10:25 am

#643713

thanks alot thats better than what i have but i need the final result to be the matchid of one match and the max average of that match

this query returns

----------

1 2.666666

2 2.000000

-------

but i need

-------

1 2.666666

--------

any ideas

JeffB Hall of Fame Points: 3541 More actions · Answer 3

Yeah, take out the outer group by matchid:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport values(1, 11619, 1, 'B')

insert @ScoutingReport values(1, 11619, 1, 'D')

insert @ScoutingReport values(1, 11619, 2, 'C')

Select 'Team Averages ' = max(avgscore)

from (select matchid, 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) avgscore

From @ScoutingReport sr

Where sr.Type = 1

And sr.ScoutId = 11619

group by matchid) s

JeffB Hall of Fame Points: 3541 More actions · Answer 4

Sorry, I missed the requirement for the matchid that includes the max-avg score.

Try this:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport values(1, 11619, 1, 'B')

insert @ScoutingReport values(1, 11619, 1, 'D')

insert @ScoutingReport values(1, 11619, 2, 'C')

create table #avgscores(matchid int, avgscore decimal(38,6))

create index ix_avgscores_avg on #avgscores(avgscore)

insert #avgscores(matchid , avgscore)

select matchid, 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) avgscore

From @ScoutingReport sr

Where sr.Type = 1

And sr.ScoutId = 11619

group by matchid

Select matchid, avgscore 'Team Averages'

from #avgscores a

inner join (select max(avgscore) maxscore

from #avgscores) x

on a.avgscore = x.maxscore

drop table #avgscores