Shawn,
Thank you. I've been working with what you provided learning as I
figure out the why of each step.
One thing I don't understand is the syntax of these 2 lines:
, sum(if(`standingpts`=3,1,0)) as wins
, sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home
Specifically the =3,1,0 in the first line and the = 'H',1.0 in the
second line.
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;
It became simple because we normalized the data.
Here is how to get your "original view" of the data:
SELECT game_id as id
, max(if(home_vis='H',coach,null)) as hcoach
, max(if(home_vis='V',coadh,null)) as vcoach
, max(if(home_vis='H',points,null)) as hscore
, max(if(home_vis='V',points,null)) as vscore
, max(if(home_vis='H',standingpts,null)) as hpts
, max(if(home_vis='V',standingpts,null)) as hpts
FROM gamestats
GROUP BY game_id;
Can you see the patterns? We are creating what is called "pivot
tables" or "crosstab queries" (depending on who you ask). It's the
flexible way of computing the statistics you want to keep.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
