×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

league table, cumulative ranking

league table, cumulative ranking

league table, cumulative ranking

(OP)
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:


CODE

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:

CODE

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close