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
