I'm attempting to write one SQL statement to retrieve data in a particular way, and don't seem to be able to do it despite dozens of attempts (indeed maybe it cannot be done), but wondered if anyone could suggest anything, such as a function I may have missed that can do it, or that it simply isn't possible!
I have to list player's histories in a football team throughout his career, and only have the fixture table and appearance table to go off: Fixtures Fixid, Fixdate, Hometeam, Awayteam 1, 10 May 2003, Lancashire, Sussex 2, 12 May 2003, Sussex, Northants 3, 15 May 2003, Essex, Durham 4, 16 May 2003, Durham, Leicestershire 5, 20 May 2003, Sussex, Derbyshire Appearances Playerid, Fixid, Teamid, Substitute? Anderson, 1, Sussex, 0 Anderson, 2, Sussex, 1 Anderson, 3, Durham, 0 Anderson, 4, Durham, 0 Anderson, 5, Sussex, 1 What I am trying to do is retrieve a count of how many games a player has started, or been substitute for, per team, per chronological spell at the team (i.e. in this instance he's played for Sussex in two separate spells, so I need that information grouped in two different returned records), such as: Playerid, Teamid, count(not a substitute), count(substitute), first_game_for_team Anderson, Sussex, 1, 1, 10 May 2003 Anderson, Durham, 2, 0, 15 May 2003 Anderson, Sussex, 0, 1, 20 May 2003 My latest SQL statement is: SELECT COUNT(*), a.playerid, a.substitute, a.teamid, f.fixdate, t.name FROM fixture f, apps a, team t WHERE a.player_id = 'Anderson' AND a.fixid = f.fixid AND a.teamid = t.teamid GROUP BY a.teamid, a.substitute ORDER BY f.fixdate DESC . but this simply creates two records per team, one for substitute appearances, one for starting appearances. Any pointers would be greatly appreciated, and if I'm asking an inappropriate question for the group please accept my apologies in advance. Many thanks Gary Broughton
