CODEWITHSUNDEEP
mysqlsqlaggregate
volting
volting

Reputation: 18967

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

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.

Upvotes: 2

Views: 600

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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;

Upvotes: 1

Rob P.
Rob P.

Reputation: 15071

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

I think LIMIT is what you want.

Upvotes: 0

Jagira
Jagira

Reputation: 1368

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

Upvotes: 0

Related Questions