List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:August 24 2010 4:43pm
Subject:RE: Calculating table standings
View as plain text  
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