Hi All.
I have a table:
CREATE TABLE games (
game_id INT NOT NULL AUTO_INCREMENT,
date(datetime),
home_team VARCHAR(30),
away_team VARCHAR(30),
home_team_goals INT,
away_team_goals INT)
with the following data:
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (1, 2011-12-12, Birmingham, Spurs, 2, 2)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (2, 2011-12-12, Liverpool, Arsenal, 1, 0)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (3, 2011-12-06, Arsenal, Birmingham, 3, 2)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (4, 2011-11-09, Man Utd, Liverpool, 0, 1)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (5, 2011-11-07, Celtic, West Ham, 4, 0)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (6, 2011-11-06, West Ham, Blackburn, 0, 0)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (7, 2011-10-24, Liverpool, Celtic, 0, 1)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (8, 2011-10-24, Man Utd, Spurs, 2, 1)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (9, 2011-10-21, Spurs, Birmingham, 1, 2)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (10, 2011-10-18, Blackburn, Arsenal, 2, 3)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (11, 2011-10-11, West Ham, Celtic, 1, 2)
INSERT into games (game_id, date, home_team, away_team, home_team_goals, away_team_goals) values (12, 2011-10-04, Arsenal, Spurs, 3, 0)
I want to create a table which shows the columns game_id, date, home_team, away_team, home_team_goals and away_team_goals, and also has four more columns:
home_game_number: This will count the number of games the home team has played prior to that date - either home or away.
away_team_number: This will count the number of games the away team has played prior to that date - either home or away.
home_team_goals_last_two: This will count the number of goals the home team has scored in the two games (or one if they’ve just played one) prior to that date - either home or away.
away_team_goals_last_two: This will count the number of goals the away team has scored in the two games (or one if they’ve just played one) prior to that date - either home or away.
Any help much appreciated! Thanks in advance.