I have a database table full of game by game results and want to know if I can calculate the following:

• GP (games played)
• Wins
• Loses
• Points (2 points for each win, 1 point for each lose)

Here is my table structure:

CREATE TABLE `results` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `home_team_id` int(10) unsigned NOT NULL,
  `home_score` int(3) unsigned NOT NULL,
  `visit_team_id` int(10) unsigned NOT NULL,
  `visit_score` int(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

And a few testing results:

INSERT INTO `results` VALUES(1, 1, 21, 2, 25);
INSERT INTO `results` VALUES(2, 3, 21, 4, 17);
INSERT INTO `results` VALUES(3, 1, 25, 3, 9);
INSERT INTO `results` VALUES(4, 2, 7, 4, 22);
INSERT INTO `results` VALUES(5, 1, 19, 4, 20);
INSERT INTO `results` VALUES(6, 2, 24, 3, 26);

Here is what a final table would look like:

+-------------------+----+------+-------+--------+
| Team Name         | GP | Wins | Loses | Points |
+-------------------+----+------+-------+--------+
| Spikers           |  4 |    4 |     0 |      8 |
| Leapers           |  4 |    2 |     2 |      6 |
| Ground Control    |  4 |    1 |     3 |      5 |
| Touch Guys        |  4 |    0 |     4 |      4 |
+-------------------+----+------+-------+--------+