group by dates

[Purfleet]

I have a number of tables in my database listing player’s appearances,
substitute appearances, player’s goals. All are in the same format of

Date, Player

I can easily run queries that list all the players with the count of
starting appearances, sub appearances and goals.

But now I need to be able to break down the appearances into different
seasons like

Adam Parker Starts Subs Goals
2004-05 24 10 5
2005-06 15 16 2
39 26 7

I have created a table listing all the seasons and the start and end dates

Season Start of Season End of Season
1985-86 24/08/1985 17/05/1986

But now I have no idea on how to extract the data

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this:

SELECT SD.Season, PI.Player, PI.Starts, PI.Subs, PI.Goals
FROM PlayerInfo As PI , SeasonDates As SD
WHERE PI.[Date] BETWEEN SD.season_start AND SD.season_end
AND PI.Player = 'Adam Parker'

Substitute your table/column names were appropriate.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJCBJ4echKqOuFEgEQIW4gCfXwc0uHNX9cwKHe2EnE0owjHzq0gAn385
oDcUsk8ZH12nBoJTiA6ff2oW
=8Esy
-----END PGP SIGNATURE-----