league table, cumulative ranking

[spperl]

Hi,

I'm working on a game by game league table calculated by looking at football results over the last 20 years. I was wondering if anyone can help me or have some ideas where I am going wrong with the following query:

select date_id, team_id, hpld hpld, htw, htd, htl, apld, atw, atd, atl
	from 
	(
	select r1.date_id, 
	       r1.home_team_id as team_id, 
	       count(*) as hpld, 
	       0 as apld,
	       sum( IF( r2.home_team_ft_score > r2.away_team_ft_score, 1, 0 )  ) htw, 
	       sum( IF( r2.home_team_ft_score = r2.away_team_ft_score, 1, 0 )  ) htd, 
	       sum( IF( r2.home_team_ft_score < r2.away_team_ft_score, 1, 0 )  ) htl,
	       0 atw,
	       0 atd, 
	       0 atl
	       
	FROM results AS r1, results AS r2
	WHERE r1.date_id >= r2.date_id
	AND r1.home_team_id = r2.home_team_id
        AND r1.season_id = r2.season_id
      	AND r1.season_id =20
	AND r1.home_team_id =79
	GROUP BY r1.date_id, r1.home_team_id
	
	UNION
	
        select r1.date_id, 
	       r1.away_team_id as team_id, 
	       0 as hpld,
	       count(*) as apld, 
	       0 as htw,
	       0 as htd,
	       0 as atw,
	       sum( IF( r2.home_team_ft_score < r2.away_team_ft_score, 1, 0 )  ) atw, 
	       sum( IF( r2.home_team_ft_score = r2.away_team_ft_score, 1, 0 )  ) atd, 
	       sum( IF( r2.home_team_ft_score > r2.away_team_ft_score, 1, 0 )  ) atl
	FROM results AS r1, results AS r2
	WHERE r1.date_id >= r2.date_id
	AND r1.away_team_id = r2.away_team_id
        AND r1.season_id = r2.season_id
      	AND r1.season_id =20
	AND r1.away_team_id =79
	GROUP BY r1.date_id, r1.away_team_id
	) as t1
GROUP BY date_id, team_id

The union on result set is to combine home results with away results.

I have limited the result set to a particular season and team. This produces the following:

date_id   team_id   hpld htw     htd     htl     apld     atw   atd     atl
---------------------------------------------------------------------------
8251 	  79 	    1 	 0 	 1 	 0 	 0 	  0 	0 	0
8258 	  79 	    0 	 0 	 0 	 0 	 1 	  0 	1 	0
8265 	  79 	    2 	 0 	 1 	 1 	 0 	  0 	0 	0
8272 	  79 	    3 	 1 	 1 	 1 	 0 	  0 	0 	0
8279 	  79 	    0 	 0 	 0 	 0 	 2 	  0 	2 	0
8293 	  79 	    0 	 0 	 0 	 0 	 3 	  0 	2 	1
8300 	  79 	    4 	 2 	 1 	 1 	 0 	  0 	0 	0
8307 	  79 	    0 	 0 	 0 	 0 	 4   	  1 	2 	1
8314 	  79 	    5 	 2 	 1 	 2 	 0 	  0 	0 	0
8328 	  79 	    0 	 0 	 0 	 0 	 5 	  1  	2 	2

I understand the group by is causing the above effect however I would prefer to track result status cumulatively so that the following result set is produced

date_id   team_id   hpld htw     htd     htl     apld     atw   atd     atl
---------------------------------------------------------------------------
8251 	  79 	    1 	 0 	 1 	 0 	 0 	  0 	0 	0
8258 	  79 	    1 	 0 	 1 	 0 	 1 	  0 	1 	0
8265 	  79 	    2 	 0 	 1 	 1 	 1 	  0 	1 	0
8272 	  79 	    3 	 1 	 1 	 1 	 1 	  0 	1 	0
8279 	  79 	    3 	 1 	 1       1 	 2 	  0 	2 	0
8293 	  79 	    3 	 1 	 1 	 1 	 3 	  0 	2 	1
8300 	  79 	    4 	 2 	 1 	 1 	 3 	  0 	2 	1
8307 	  79 	    4 	 2 	 1 	 1 	 4   	  1 	2 	1
8314 	  79 	    5 	 2 	 1 	 2 	 4 	  1 	2 	1
8328 	  79 	    5 	 2 	 1 	 2 	 5 	  1  	2 	2

i.e. a running total of wins / draws / losses.

Does anyone have any hints or tips as to what I could do to get the desired result set?

Many thanks,
spperl