I have three tables for my NFL database
Players - which has player id/name
stats_passing - which has player id, game_id, yards and touchdowns by game
Game - which has game date
I'm trying to get the top four players with the most yards and touchdowns in the last five games they've played in.
I have this so far:
SELECT
p.name,
SUM(s.yds) AS tot_yds,
SUM(s.tds) AS tot_tds
FROM players AS p
INNER JOIN (
SELECT
s.id,
s.player_id,
s.yds,
s.tds
FROM stats_passing AS s
INNER JOIN games AS g ON s.game_id = g.id
WHERE YEAR(g.date) = 2014
ORDER BY s.player_id, g.date DESC
LIMIT 5) AS s ON p.id = s.player_id
ORDER BY tot_yds DESC, tot_tds DESC
LIMIT 4
However it's only returning one result.
Drew Brees, 1497, 10
But as I said above, I want the top four players sorted by their total yards and total touchdowns similar to
Drew Brees, 1497, 10
Tom Brady, 1234, 9
Andrew Luck, 1123, 8
Aaron Rodgers, 1023, 7
I've been working on this for days and I think my mysql knowledge is at its end. Please help! Thanks in advance!
Figured it out. Hope this helps someone else. Feel free to add to it if it's inefficient.
SET @rn = 0;
SET @prev = 0;
SELECT
name,
tot_yds,
tot_tds
FROM (
SELECT
p.name,
SUM(s.yds) AS tot_yds,
SUM(s.tds) AS tot_tds
FROM players AS p
INNER JOIN (
SELECT
s.id,
s.player_id,
s.yds,
s.tds,
@rn := CASE WHEN @prev = s.player_id
THEN @rn + 1
ELSE 1
END AS rn,
@prev := s.player_id
FROM (SELECT
st.id,
st.player_id,
st.yds,
st.tds
FROM stats_passing AS st
INNER JOIN games AS g ON st.game_id = g.id
WHERE YEAR(g.date) = 2014
ORDER BY st.player_id, g.date DESC) AS s
) AS s ON p.id = s.player_id
WHERE rn <= 5
GROUP BY p.name) AS ns
ORDER BY tot_yds DESC, tot_tds DESC
LIMIT 4