AVG with TOP (?) - 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

Transact-SQL (2000)

AVG with TOP (?)

Author

Topic

ALBean
Starting Member

12 Posts

Posted - 2004-05-28 : 04:13:53

Is it possible to get the average when using TOP? I have a table with two columns: rDate, RainFall.

I want to get the average rainfall over the past N days (here N=200).

Something like:

Select Top 200 avg(RainFall) from RainTable order by rDate

srinivasanr
Starting Member

15 Posts

Posted - 2004-05-28 : 04:59:48

This could do..

SELECT AVG(RainFall) FROM RainTable
WHERE Empsalary
IN (SELECT TOP 200 RainFall FROM RainTable )

Wallops!!!

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-05-28 : 07:52:23

Srini the solution is right ,To add a few note to ur query

SELECT AVG(RainFall) FROM RainTable
WHERE RainFall
IN (SELECT TOP 200 RainFall FROM RainTable Order by rDate DESC)

The above query has been corrected as the column "Empsalary" is replaced with RainFall . And order Clause is required as they are considering the past 200 days.

Note :If the value of N is an input parameter then u need to opt for dynamic sql as TOP clause works with a constant value.

Enjoy working

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-28 : 07:56:16

You should use a where clause, just filter for the range of days you wish to calculate the average for.

select avg(rainfall)
from Raintable
where rDate Between dateadd(dd,-200,getdate()) and getdate()

or something like that . always use the data to determine your results when possible, as opposed to getting the "top x rows" from a table.

- Jeff

ALBean
Starting Member

12 Posts

Posted - 2004-05-28 : 11:25:30

Thanks Guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources