I think your match table has all the information necessary to display the
results you want. Since each record contains data for two teams (home and
away), you'd probably need to select each separately and union the results
together before summarizing. Your query might look something like the
following:
select
seasons_id
,team_id
,count(*) as games_played
,sum(home) as home_games_played
,sum(away) as away_games_played
,sum(won_home) as won_home
,sum(draw_home) as draw_home
,sum(lost_home) as lost_home
,sum(scored_home) as scored_home
,sum(conceded_home) as conceded_home
,sum(won_away) as won_away
,sum(draw_away) as draw_away
,sum(lost_away) as lost_away
,sum(scored_away) as scored_away
,sum(conceded_away) as conceded_away
from
(select
seasons_id
,home_team_id as team_id
,1 as home
,0 as away
,if(home_goals > away_goals, 1, 0) as won_home
,if(home_goals = away_goals, 1, 0) as draw_home
,if(home_goals < away_goals, 1, 0) as lost_home
,home_goals as scored_home
,away_goals as conceded_home
,0 as won_away
,0 as draw_away
,0 as lost_away
,0 as scored_away
,0 as conceded_away
from matches
union all
select
seasons_id
,away_team_id as team_id
,0 as home
,1 as away
,0 as won_home
,0 as draw_home
,0 as lost_home
,0 as scored_home
,0 as conceded_home
,if(away_goals > home_goals, 1, 0) as won_away
,if(away_goals = home_goals, 1, 0) as draw_away
,if(away_goals < home_goals, 1, 0) as lost_away
,away_goals as scored_away
,home_goals as conceded_away
from matches) s1
group by seasons_id, team_id;
-----Original Message-----
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Tuesday, August 24, 2010 7:45 AM
To: [MySQL]
Subject: Calculating table standings
Hi,
I wondered if anyone can offer me some help with regards the following issue
I'm having. Basically, I've the following table structure containing rows
of results between two football teams. The fields are
match_id
seasons_id
week_number
home_team_id
away_team_id
home_goals
away_goals
Based on the above information, I'm wanting to generate a league table
listing showing
games_played
won_home
draw_home
lost_home
scored_home
conceded_home
won_away
draw_away
lost_away
scored_away
conceded_away
Finally I also want a tally for the number of points e.g 3 points for win, 1
point for a draw. Do you think this is possible with the basic table I
have, or should I consider putting the result data in a leagues table
working out the fields I have listed above, and then just calculating it and
display it ?
Thanks for any advice.
Cheers
Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
