Thanks for your great response Travis. This is exactly what I was after. On Thu, Aug 26, 2010 at 6:56 PM, Travis Ard <travis_...@hotmail.com> wrote:
> I don't think there's anything inherently wrong with the way you've > designed > your table to store your match data. I don't have experience designing > these kinds of applications, so maybe some others might have better advice > for you. If you find your reporting is too slow or it is too awkward to > query this table twice and union the results, then you might want to add a > summary table. > > -Travis > > -----Original Message----- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, August 25, 2010 2:06 PM > To: Travis Ard > Cc: [MySQL] > Subject: Re: Calculating table standings > > Travis > > Do you think it would be better if I stored the information in > a separate table, rather than using unions etc - to make the searching, > counting etc easier ? Or is this method a standard way of dealing with > this > sort of data. > > 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 > > > > > >
