CODEWITHSUNDEEP
mysql
user746461
user746461

Reputation:

How to get average of each player in recent 5 games

I have a table containing player name, total on court time, and date of that game.

enter image description here

Given a list of players, I want to calculate the average on court time of each player in recent 5 games.

This is how I get average on court time of Aaron Brooks.

select `Player Name`, AVG(TIME_TO_SEC(`Total on Court`))
from
    (SELECT * FROM nba.`player stat total`
    where `Player Name`='Aaron Brooks'
    order by `date` desc
    limit 5) as t

But I have a list of players, it is possible to do in pure sql rather than executing sql in a for-loop in my program?

The players may be in the form ('Aaron Brooks', 'Chris Bosh', 'Andre Miller') or any other form you like.

Upvotes: 1

Views: 615

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use user-defined variables to get 5 recent records per player group

SELECT t.`Player Name` ,
AVG(TIME_TO_SEC(t.`Total on Court`))
 FROM (
SELECT s.*,
@rank:= CASE WHEN @group = s.`Player Name` THEN @rank +1 ELSE 1 END rank ,
@group:= s.`Player Name` g
 FROM
`player stat total` s
JOIN (SELECT @group:='',@rank:='') t
 WHERE s.`Player Name` IN('Aaron Brooks', 'Chris Bosh', 'Andre Miller')
ORDER BY `Player Name`  ,`date` DESC
) t WHERE t.rank <=5
GROUP BY t.`Player Name`

Upvotes: 0

pkatsourakis
pkatsourakis

Reputation: 1082

Give this a try

SELECT *
FROM
    (SELECT `Player Name`, SUM(AVG(TIME_TO_SEC(`Total on Court`))) AS Avg FROM nba.`player stat total`
    GROUP BY `Player Name`
    ORDER BY `date` desc
    LIMIT 5) AS t

Upvotes: 0

Related Questions