Okay, I'm trying to build a query that will return a conference standings table.
The end result I want to have is:
TEAM | OVERALL WINS | OVERALL LOSSES | CONFERENCE WINS | CONFERENCE LOSSES | WINNING PCT.
Here is the table layout I currently have (let me know if I should make changes)...
--------------------------------------------------------
--
-- Table structure for table `2sta`
--
CREATE TABLE IF NOT EXISTS `2sta` (
`ID` int(5) NOT NULL auto_increment,
`Date` date NOT NULL,
`CleanDate` text NOT NULL,
`Sport` text NOT NULL,
`Conf` text NOT NULL,
`Home` text NOT NULL,
`HScore` int(5) NOT NULL,
`Away` text NOT NULL,
`AScore` int(5) NOT NULL,
PRIMARY KEY (`ID`),
FULLTEXT KEY `Conf` (`Conf`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `2sta`
--
INSERT INTO `2sta` (`ID`, `Date`, `CleanDate`, `Sport`, `Conf`, `Home`, `HScore`, `Away`, `AScore`) VALUES
(1, '2011-01-28', 'Jan 28', 'Boys Basketball', 'Y', 'Eddyville', 0, 'Centerville', 1),
(2, '2011-01-28', 'Jan 28', 'Boys Basketball', 'N', 'Centerville', 1, 'Moravia', 0);
What I would need the query to do is count the times a team scores more points than the other, count the times a team scores less points, the times a team scores more points in a conference game (where conference = Y), the times a team scores less points in a conference game.
I would then need to have the overall wins be divided by total games to get winning percentage.
