Selecting Average of top 3 numbers.

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)

Selecting Average of top 3 numbers.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-11 : 07:47:19

Chris writes "How do I get the AVERAGE of the top 3 numbers in one column of a table?

In a previous email you showed how to get the top 3 numbers by doing the following:

SELECT TOP 3 NumMiles
FROM Excercise
WHERE [some condition]

I want to average these numbers and have tried some of the following variations that do not work (btw: Just the plain "Top 3 NumMiles" works fine for me):

SELECT AVG(TOP 3 NumMiles) AS AvgMiles
FROM Excercise
WHERE [some condition]

SELECT TOP 3 NumMiles AS AvgMiles, AVG(AvgMiles)
FROM Excercise
WHERE [some condition]

SELECT TOP 3 NumMiles, AVG(NumMiles)
FROM Excercise
WHERE [some condition]"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-11 : 08:08:51

Chris

Try something like this

SELECT AVG(e.NumMiles)
from Exercise e,
(SELECT TOP 3 NumMiles FROM Exercise WHERE [some condition]) z
WHERE t.miles = z.miles

Raymond

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-11 : 08:09:15

Try

SELECT AVG(NumMiles) AS AvgMiles
FROM (
SELECT TOP 3 NumMiles
FROM Excercise
WHERE [some condition]
) x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources