Average of a column for the bottom N rows for each group in a table

Question

I want to get the average of a column for the last 5 rows (if there are less than 5 rows (games) for a player then it should return nothing..)

If I could use a subquery then it would be straight forward, but since this isn't supported Im not sure how to approach it.

The sql below is what I have so far, this will get me the average for all games for each player if the player has played more than 5 games.

CREATE VIEW last_5_avg
AS
   SELECT player, avg(score) FROM game_stats
 JOIN games
ON games.id = games_stats.id GROUP BY player HAVING COUNT(games.id) > 4
ORDER BY games.id DESC;

Looks like the only way around this problem is to create a view for the last 5 game_stats of each player and then use a second view to do the averages.

score 1 · Answer 1 · answered Mar 12 '11 at 23:47

1

select player, avg(score) 
from game_stats
inner join games g0 on g0.id = games_stats.id 
where g0.id in (
    select g1.id
    from games g1
    where g1.id = g0.id
    order by g1.id desc
    limit 5
    )
group by player 
having count(g0.id) > 4
order by g0.id desc;

answered Mar 12 '11 at 23:47

Clodoaldo Neto

118,695
26
233
260

I appreciate the attempt but this won't work for a number of reasons, 1)subquerys aren't supported in views, 2)LIMIT isn't supported in subquerys 3) The subquery only selects the bottom 5 rows, I need to select the bottom 5 of each group in the table, like this http://stackoverflow.com/questions/3998529/selecting-top-n-rows-for-each-group-in-a-table . – volting Mar 13 '11 at 11:29

score 0 · Answer 2 · answered Mar 12 '11 at 18:59

0

Use LIMIT.

XXXXXXX ORDER BY games.id DESC LIMIT 5;

Make sure that you have your conditions in the following order -

HAVING ---> ORDER BY ---> LIMIT

Reference: http://dev.mysql.com/doc/refman/5.1/en/select.html

answered Mar 12 '11 at 18:59

Jagira

1,368
2
14
29

I tried LIMIT already, but It didnt work, as far as I can see it just limits the number of rows put in to the view not the number of rows averaged. – volting Mar 12 '11 at 19:01

score 0 · Answer 3 · answered Mar 12 '11 at 19:00

0

http://dev.mysql.com/doc/refman/5.0/en/select.html

I think LIMIT is what you want.

answered Mar 12 '11 at 19:00

Rob P.

14,921
14
73
109

Average of a column for the bottom N rows for each group in a table

3 Answers3