Cannot perform an aggregate function

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

SQL Server 2000 Forums

Transact-SQL (2000)

Cannot perform an aggregate function

Author

Topic

jamespowers
Starting Member

3 Posts

Posted - 2006-08-17 : 13:23:12

Hello everyone,

Can someone help with this? I am trying to get an average balance and then total it between a range....But I continue to get the below error message...

select
sum(case when avg(cast(current_balance as money)) <= 0 and avg(cast(current_balance as money)) < 10 then 1 else 0 end) as range0
from stage

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-17 : 13:33:14

>> Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
That's the reason.
You are trying to sum an expression containing an avg.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-17 : 13:38:52

So try this:

select	sum(rangecalc) as range0
from	(select	case	when avg(cast(current_balance as money)) <= 0
				and avg(cast(current_balance as money)) < 10
				then 1
			else 0 end as rangecalc
	from stage) subquery

jamespowers
Starting Member

3 Posts

Posted - 2006-08-17 : 14:03:16

sorry, but what is meant by the subquery

select sum(rangecalc) as range0
from (select case when avg(cast(current_balance as money)) <= 0
and avg(cast(current_balance as money)) < 10
then 1
else 0 end as rangecalc
from stage) subquery

jamespowers
Starting Member

3 Posts

Posted - 2006-08-17 : 14:07:57

And how would I do multiple ranges

0-100
101-200
201-300
301-400
401-500
etc...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-17 : 14:27:29

james -- why don't you step back, give us your table structures and some representative sample data and tell us what you need to do.

- Jeff

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:27:16

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources