Calculating table standings

Calculating table standings

am 24.08.2010 15:45:01 von Tompkins Neil

--0016362843bac5ed25048e91f8bb
Content-Type: text/plain; charset=ISO-8859-1

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

--0016362843bac5ed25048e91f8bb--

RE: Calculating table standings

am 24.08.2010 18:43:46 von Travis Ard

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@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=gcdmg-mysql-2@m.gmane.org

Re: Calculating table standings

am 24.08.2010 19:05:41 von Tompkins Neil

--0015175cb05873575d048e94c662
Content-Type: text/plain; charset=ISO-8859-1

Travis

Thanks for the response. Do you recommend I store the data in this way ?
Or should I look at storing in a separate leagues table, why by I list all
the data in the separate columns for each round and then just compute a
fairly basic query ? What is the recommend way ?

Cheers
Neil

On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard 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@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
>
>

--0015175cb05873575d048e94c662--

Re: Calculating table standings

am 25.08.2010 22:06:12 von Tompkins Neil

--0016364ec7ecd1f7ea048eab69c2
Content-Type: text/plain; charset=ISO-8859-1

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 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@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
>
>

--0016364ec7ecd1f7ea048eab69c2--

RE: Calculating table standings

am 26.08.2010 19:56:47 von Travis Ard

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@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 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@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=gcdmg-mysql-2@m.gmane.org

Re: Calculating table standings

am 06.09.2010 16:08:39 von Tompkins Neil

--0016364edb1c39c8d2048f97d111
Content-Type: text/plain; charset=ISO-8859-1

Thanks for your great response Travis. This is exactly what I was after.

On Thu, Aug 26, 2010 at 6:56 PM, Travis Ard 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.tompkins@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
> 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@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
> >
> >
>
>

--0016364edb1c39c8d2048f97d111--