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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
