Need help with AVG query - SQL Server Forums

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

SQL Server Development (2000)

Need help with AVG query

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-03-06 : 19:02:46

Hi,
I have a table which contains a column that should be set to the average of the sum of a group of rows from that table. Like this:


TYPE  TOTAL AVGTYPE
1     98    0
1     75    0
1     60    0
1     55    0
2     88    0
2     10    0
2     5     0

I'd like to set the AVGTYPE column to the average of the SUM of the TOTAL column per TYPE.

But I'm having difficulty with the query since the inner query needs a value from the "outer" UPDATE query (namely the value of TYPE).

Like this:

UPDATE TBL SET AVGTYPE = (
SELECT AVG(TOTAL) FROM TBL GROUP BY TYPE HAVING (TYPE=TheTypeOfTheCurrentRowBeingUpdated))

.... confusion.

all help greatly appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 19:15:32

[code]
Update TBL
SET AVGTYPE = a.AVGCALC
FROM TBL inner join (Select AVG(TOTAL) as AVGCALC, [TYPE]
FROM TBL
GROUP BY [TYPE]) a
on TBL.[TYPE] = a.[TYPE]
[/code]

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources