Travis Thanks for the response. Do you recommend I store the data in this way ? Or should I look at storing in a separate leagues table, why by I list all the data in the separate columns for each round and then just compute a fairly basic query ? What is the recommend way ?
Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard <travis_...@hotmail.com> wrote: > 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 > >
