Hi All,

Basically I run a small website for a soccer league. The league has been going 5 or 6 years and each year I create a new set of tables. So I have a table called stats2008,stats2009,stats2010, etc. I have a SQL statement that looks at the stats table and calculates the goals and assists and spits out a leaderboard for the season. However, I would also like to be able to have a statement that can do the same thing but calculate using each seasons stats to get an all-time leaderboard using the tables from previous seasons? Looking back in an ideal world I probably should have just used one table for this and had a column that specified the current season however I only do this on the side for fun so I guess I didint have that longterm visison.

Is this possible? Or would I need to redesign the tables? My SQL statement for the current season stats is below (the $variable would specify the current year via code):

Code:
SELECT p.id
    , p.player
    , p.team
    , p.mugshot
    , coalesce(sum(s.goals),0) as goals
    , coalesce(sum(s.assists),0) as assists
    , coalesce(sum(s.fouls),0) as fouls
    , coalesce(sum(s.assists + s.goals),0) as points
    , t.teamname
FROM $league as p
LEFT OUTER JOIN (SELECT goals
        , assists
        , fouls
        , player_id
    FROM $stats) as s
    on s.player_id = p.id
LEFT OUTER JOIN (SELECT id
       ,teamname
     FROM $teams) as t
     on t.id = p.team
GROUP
    BY p.id
    , p.player
ORDER BY assists DESC
LIMIT 0,10