Results 1 to 7 of 7
Nov 8, 2013, 10:21 #1
- Join Date
- Nov 2004
- 0 Post(s)
- 0 Thread(s)
How to combine multiple historic tables to get an "all time" stats list?
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):
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