List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:August 25 2010 8:06pm
Subject:Re: Calculating table standings
View as plain text  
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_ard@stripped> 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.tompkins@stripped]
> 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
>
>

Thread
Calculating table standingsTompkins Neil24 Aug
  • RE: Calculating table standingsTravis Ard24 Aug
    • Re: Calculating table standingsTompkins Neil24 Aug
    • Re: Calculating table standingsTompkins Neil25 Aug
      • RE: Calculating table standingsTravis Ard26 Aug
        • Re: Calculating table standingsTompkins Neil6 Sep