Albert Padley <[EMAIL PROTECTED]> wrote on 07/11/2005 02:30:51 PM:
> First, I have spent hours searching the web and the list archives and
> can't find anything helpful.
>
> Second, I'm using MySql 4.0.24
>
> Third, I'm still a novice at query formulation, so be kind.
>
> The Problem---
>
> I'm working with a new youth soccer league. I need to generate weekly
> standings that includes total wins, losses and draws for each team.
> Additionally, I also need to calculate the standings. In most soccer
> leagues, including this one, a winning team receives 3 points, a tie
> gets 1 point and a loss get 0 points.
>
> The pertinent table structure follows:
>
> CREATE TABLE `games` (
> `id` int(11) NOT NULL auto_increment,
> `hcoach` varchar(20) NOT NULL default '',
> `vcoach` varchar(20) NOT NULL default '',
> `hscore` tinyint(4) default NULL,
> `vscore` tinyint(4) default NULL,
> `hpts` tinyint(4) default NULL,
> `vpts` tinyint(4) default NULL,
> PRIMARY KEY (`id`),
> KEY `hscore` (`hscore`,`vscore`,`hpts`,`vpts`)
> ) TYPE=MyISAM AUTO_INCREMENT=4162 ;
>
> I located the following query. Unfortunately, it uses subselects
> which aren't available in 4.0.24
>
> SELECT team, SUM( wins ) , SUM( losses )
> FROM (
>
> (
>
> SELECT hcoach AS team, SUM(
> IF (
> hscore > vscore, 1, 0
> ) ) AS wins, SUM(
> IF (
> vscore > hscore, 1, 0
> ) ) AS losses
> FROM games
> GROUP BY team
> )
> UNION (
>
> SELECT vcoach AS team, SUM(
> IF (
> vscore > hscore, 1, 0
> ) ) AS wins, SUM(
> IF (
> hscore > vscore, 1, 0
> ) ) AS losses
> FROM games
> GROUP BY team
> )
> GROUP BY team
> ORDER BY losses
>
> I would think this would be a fairly common issue. However, I don't
> know enough to even know where to start looking. How do I accomplish
> this?
>
> Thanks.
>
> Albert Padley
>
I think one of the easiest ways to approach the statistical analysis is to
normalize your `games` table by eliminating the duplicate sets of
information.
CREATE TABLE gamestats (
`game_id` INT,
`home_vis` char(1) default='V',
`coach` varchar(20) NOT NULL default '',
`points` tinyint(4) NOT NULL default=0,
`standingpts` tinyint(4) default=0,
Primary Key (`game_id`, `coach`)
);
INSERT gamestats (game_id, homevis, coach, points, standingpts)
SELECT `id`,'H',`hcoach`, `hscore`, `hpts`
FROM `games`;
INSERT gamestats (game_id, homevis, coach, points, standingpts)
SELECT `id`,'V',`vcoach`, `vscore`, `vpts`
FROM `games`;
Now it will be much easier to compute the statistics. For example, this
query will give you the # of games one, # of games won at home, Total
points for season, Avg points per game, and total rank (in descending
order)
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
