Albert Padley <[EMAIL PROTECTED]> wrote on 07/11/2005 07:20:04 PM:
> OK. I'm responding to my own post since I figured out the syntax.
> This allowed me to figure out how to compute the ties as well as the
> wins. How to I compute the losses. I can't use the same technique
> because I'd be looking for '0' in the standingpts column and that
> column defaults to '0'. Thus it would also be counting the games that
> have yet to be played. It would seem to involve simple arithmetic of
> games - wins - ties = losses, but I can't seem to get the syntax
> correct.
>
> One final question. We get the total goals scored in the season by sum
> (`points`) as total_points. How would I find the total goals scored
> against a team in the context of the query below?
>
> Thanks.
>
> Albert Padley
>
>
> On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote:
>
> >
> > SELECT `coach`
> > , sum(`standingpts`) as standings
> > , count(`game_id`) as games
> > , sum(if(`standingpts`=3,1,0)) as wins
> > , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as
> > wins_at_home
> > , sum(`points`) as total_points
> > , avg(`points`) as avg_points
> > FROM `gamestats`
> > GROUP BY `coach`
> > ORDER BY `standings` DESC;
> >
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
>
Hi Albert,
I didn't catch it last time but this line
, sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home
^
is missing a parenthesis here --------------------|
and should have read:
, sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0)) as wins_at_home
Sorry for any confusion.
Goals by the other team is not something you can derive from the
`gamestats` table we created in a single query (it would require you to
combine at least two queries or use a subquery) but if you added another
column to the `gamestats` table, you could compute that value from a
single query. I guess you could call it `opp_points` or something similar.
Just populate it with the score from the other team. That would also make
computing the "losing margin" for each coach rather simple. Here is how
you would find the 5 worst trouncings of the season (so far) using the new
field:
SELECT coach, (opp_points - points) as losing_margin
from gamestats
order by losing_margin DESC
limit 5;
How would I determine Win/Lose/Draw if the game hasn't been played yet? I
was unaware that you needed statistics entries for games that haven't been
played (is this something new?). This is not the `game` table where you
could schedule games and such. This is the table where you record the
facts about each coach from a game. I would somehow mark the record on the
`game` table that the game has been decided. Then, if I didn't have
matching records in my `gamestats` table, I would know I needed to do some
data entry. I wouldn't try to use `gamestats` to track game completion. I
guess we really should rename that table `coachstats` as it accurately
describes each coach's performance (not the game itself); but that would
be up to you.
Just as a further development idea, you could make up a similar table for
the player's stats, too. You could make fields like: game_id, team_id,
goals, saves, assists, time_played, redcards, yellowcards, etc. Same
process, different table for different facts (player stats not coaching
stats).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
