Hi, Since i don't know what is "played for Sussex in two separate spells" -- what's a spell ? (sorry) -- i assume it's a different period of time, but don't know how to calculate it.
Ignoring the "spell", i did this: SELECT A.Playerid, A.Teamid, sum(if (A.Substitute=1,0,1)) not_a_substitute, sum(if (A.Substitute=1,1,0)) substitute, min(F.fixdate) FROM Fixtures F, Appearances A WHERE F.Fixid=A.Fixid AND (A.Teamid=F.Hometeam OR A.Teamid=F.Awayteam) GROUP BY A.Teamid ORDER BY F.Fixdate For shure this isn't the best query, but maybe it can turn on some lights. My data was: (i didn't use table Team or Player, used directly names): mysql> select * from Fixtures; +-------+------------+----------------+----------------+ | Fixid | Fixdate | Hometeam | Awayteam | +-------+------------+----------------+----------------+ | 1 | 2003-05-10 | Lancashire | Sussex | | 2 | 2003-05-12 | Sussex | Northants | | 3 | 2003-05-15 | Essex | Durham | | 4 | 2003-05-16 | Durham | Leicestershire | | 5 | 2003-05-20 | Sussex | Derbyshire | | 6 | 2003-05-21 | Leicestershire | Derbyshire | +-------+------------+----------------+----------------+ 6 rows in set (0.01 sec) mysql> select * from 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 | +----------+-------+--------+------------+ 5 rows in set (0.01 sec) mysql> SELECT A.Playerid, A.Teamid, sum(if (A.Substitute=1,0,1)) not_a_substitute, sum(if (A.Substitute=1,1,0)) substitute, min(F.fixdate) FROM Fixtures F, Appearances A WHERE F.Fixid=A.Fixid AND (A.Teamid=F.Hometeam OR A.Teamid=F.Awayteam) GROUP BY A.Teamid ORDER BY F.Fixdate; +----------+--------+------------------+------------+------------+ | Playerid | Teamid | not_a_substitute | substitute | fixdate | +----------+--------+------------------+------------+------------+ | Anderson | Sussex | 1 | 2 | 2003-05-10 | | Anderson | Durham | 2 | 0 | 2003-05-15 | +----------+--------+------------------+------------+------------+ 2 rows in set (0.01 sec) On Wed, 2003-06-25 at 17:40, Gary Broughton wrote: > 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 > -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
