List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:August 26 2010 5:56pm
Subject:RE: Calculating table standings
View as plain text  
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.tompkins@stripped] 
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_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