Cannot perform an aggregate function on an express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Cannot perform an aggregate function on an express

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-06-14 : 09:56:35

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 10:03:11

[code]
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
[/code]

Madhivanan

Failing to plan is Planning to fail

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-06-14 : 10:08:52

thanks a lot thats prefect but can i just ask what the T at the end represents and why it doesnt work when i remove it

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-14 : 10:16:08

The T is a derived table alias. The first bit of this article explains it...

http://www.sqlteam.com/item.asp?ItemID=6692

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 10:20:12

Read about Derived tables in sql server help file

Madhivanan

Failing to plan is Planning to fail

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-06-14 : 10:25:57

thanks alot folks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources