TOP 5 & AVG?

# Thread: TOP 5 & AVG?

1. Senior Member
Join Date
Dec 1969
Posts
106

## TOP 5 & AVG?

I have this code, which gets the top 5 test scores.<BR><BR>SELECT TOP 5 TestScore FROM tblEmployeePerformances<BR><BR>Now I need to make it so that it finds the average of those scores. For instance if the scores were 90, 90, 80, 80 and 85, the results would return 85.<BR><BR>I have seen the syntax somewhere before but I can&#039;t find it anywhere. Can anyone help?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## It would take 2 queries...

...so why not just calculate the average in your ASP code?<BR><BR>Adding 5 numbers together and then dividing by 5 isn&#039;t that tough.<BR><BR>Ehhh...you could do it in one query with SQL Server, I guess. But you don&#039;t say what DB you are using.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: TOP 5 & AVG?

select avg(testScore) from <BR>(select top 5 testScore FROM tblEmployeePerformances<BR> order by testScore desc) s<BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oh...I thought he needed...

...the average *AND* the 5 top scores. Not just the average.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
106

## RE: TOP 5 & AVG?

Did your post get cut off? DOn&#039;t you need to have some sort of "AS" statement when using an aggregate function? In other words:<BR><BR>select avg(testScore) AS AverageTestScore from <BR>(select top 5 testScore FROM tblEmployeePerformances<BR>order by testScore desc)

6. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Not required.

You can refer to the field by number, rather than by name:<BR><BR>&#060;%=RS(0) %&#062;<BR><BR>Or..<BR>&#060;%=RS("FieldName") %&#062;<BR><BR>It&#039;s really all the same.

7. Senior Member
Join Date
Dec 1969
Posts
106

## RE: Oh...I thought he needed...

Well..I need the average of the TOP 5. In other words if all the scores are 90, 90, 85, 80, 80, 75, 65, 60 I need the result to be 85 because it would only select the five highest scores.

8. Senior Member
Join Date
Dec 1969
Posts
106

## RE: Not required.

I am getting a "Incorrect syntax near &#039;)&#039;" error. I am using SQL Server by the way.

9. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## What is your SQL query..

.. .that you are now using?

10. Senior Member
Join Date
Dec 1969
Posts
106

## RE: What is your SQL query..

SELECT AVG(TestScores) FROM (SELECT TOP 5 TestScores FROM tblEmployeePerformances ORDER BY TestScores DESC)<BR><BR>Basically I need to get the average of the 5 highest scores

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•