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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
