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!

*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.

Jobs

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!

Resources

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