Mysql – Per Team – Count Number of Losses in Last N Results

MySQL

I have the following tables:

teams:
-----------------------
| team_id | team_name |
-----------------------

team_results:
----------------------------------------------------------
| team_results_id | team_id | result_date | won (1 OR 0) |
----------------------------------------------------------

For each team, I want to look at the last N results and count the number of losses.

So far, I have the following query:

SELECT *, COUNT(*)
FROM
(
    SELECT teams.team_id, won
    FROM teams
    INNER JOIN
    (
        SELECT team_id, won
        FROM team_results
        ORDER BY result_date DESC
    ) AS team_results ON teams.team_id = team_results.team_id
) AS a
WHERE won = 0
GROUP BY team_id;

This counts the total number of losses per team, but I've been unable to limit the count to the last N results for each team. I've tried placing a limit in the nested subquery without success.

Best Answer

This should do the trick:

select team_id, count(*) from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id 
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10 and won = 0
group by team_id;

You use the inner query to enumerate the results by date. Here you probably want to apply some heuristic where condition to filter the results to optimize your query. For example if they in average play every second day then you can limit for the last one month or so.

Then you can count the lost games with the given conditions per team.

@update to show teams with 0 loss

select team_id, sum(1 - won) -- instead of count rows sum losses (1-won) = loss
from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id 
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10  -- remove the won=0 filter 
group by team_id;
Related Question