Summing and then averaging rows

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)

Summing and then averaging rows

Author

Topic

obsessedandre
Starting Member

2 Posts

Posted - 2001-12-03 : 01:53:55

Hi everyone.

I have a situation where, I have thousands of rows in a database, which contain bigint values, and an ID column. There are several dozen different IDs. I want to combine the value of all rows with the same ID, and then find the average value of the combined rows. How would I write this in SQL? I tried it as:

SELECT @AverageSum = AVG(SUM(mytable.BigIntValue))

FROM

mytable WHERE

mytable.ID = mytable.ID GO

but then I get: "Cannot perform an aggregate function or an expression containing an aggregate or a subquery"

But I've yet to figure out any way of doing it. Is this doable? Any help is greatly appreciated. Thanks alot.

Nazim
A custom title

1408 Posts

Posted - 2001-12-03 : 04:20:41

select avg(bigintvalue) from mytable -- should do it for all of them

if u want the result id wise

select id,avg(bigintvalue) from mytable
group by id

will give u list of id's with the corresponding Average values.

HTH

-------------------------

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-12-03 : 04:23:14

select @averageSum = avg(s)
from (select sum(BigIntValue) as s
from MyTable
group by id ) as q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources