Find Average of first 20% of values

[HZ]

I am trying to find the average of the first 20% of the records:

e.g. if there are 20 records I am trying to find the average of the lowest
4, or first 4 in ascending order??

Do i need to run a seperate query to calculate the first 20% of records,
then use a seperate function to calculate the average of these records?

Thank you

 

[Allen Browne]

You can do it all in one query by using a subquery, though it would probably
be much more efficient to stack one query on top of another.

This kind of thing:

SELECT Avg(Amount) AS AvgAmount
FROM Table1
WHERE Table1.ID IN
(SELECT TOP 20 PERCENT ID
FROM Table1 AS Dupe
ORDER BY Dupe.Amount DESC , Dupe.ID);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html