calculating average of first 'n' records

D

delali

I have a table with states and numbers and i want to find the average of the
first n occurances of each state.
example:
boston 10
boston 20
boston 50
new york 30
new york 60
new york 100
new york 20

i want a query that gives me the average of the first 2 occurances of each
state. boston = 15 and new york = 40. or the first 3 occurances and so on.

can someone please help?
 
J

John Spencer (MVP)

Well, how do you determine the first two occurences? Is there a date field or
do you just want the lowest scores? What about ties? The rough idea for doing
this is to use a correlated sub-query and a TOP clause.

SELECT City, Avg(Score) as ScoreAvg
FROM TableName
WHERE Score IN (
SELECT TOP 2 Scores
FROM TableName as Temp
WHERE Temp.City = TableName.City
ORDER BY Score)
GROUP BY City

This should give you
Boston 15
New York 45

If you had Boston scores of 10, 20, 20, 20, 20 then it would give you
Boston 18 (not 15) since it would return the ten and all the 20's (90/5)=18
 
Top