SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to combine multiple historic tables to get an "all time" stats list?

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if you want overall stats for multiple tables, use a UNION query instead of FROM $stats

    e.g.
    Code:
      FROM $league as p
    LEFT OUTER 
      JOIN ( SELECT goals
                  , assists
                  , fouls
                  , player_id 
               FROM stats2008
             UNION ALL
             SELECT goals
                  , assists
                  , fouls
                  , player_id 
               FROM stats2009
             UNION ALL
             SELECT goals
                  , assists
                  , fouls
                  , player_id 
               FROM stats2010  ) as s
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I figured this out searching through another thread. I had tried with UNION but UNION ALL gives me what I need.

    This may be complicating things a bit, but is there anyway I can count the number of teams the particular player has played for? In the first statement I Left Outer Join the teams table but this would only be for a specific year. It would be great in the results if I could also return a number of number of teams, this would essentially give me the number of seasons played

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tdob View Post
    ... is there anyway I can count the number of teams the particular player has played for?
    if you examine your query results, i think you'll find that any player who has played for more than one team, either in your original query looking at just one year, or the query with the UNION, will have incorrect results, because of the "cross join effects" of each of the player's teams being matched with each row of stats for that player

    my advice is, remove the join to teams altogether (in both versions of the query) and check your results

    if you want number of a player's teams for your leaderboard, i can help you add that after you have completely verified the accuracy of the results without it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You were right. I removed teams all together. I just discovered an error though. While the results are correct, it only displays those players who are playing in the current season as I am doing the initial select statement on the current years "league" table which contains all the players names and mugshots. I am thinking it is best to just load all players into this years table and keep doing that each year?

    I currently have

    Code:
    SELECT p.id
        , p.player
        , 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
    FROM league2014 as p
    LEFT OUTER JOIN (SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2014
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2013
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2012
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2011
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2010
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2009
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats2008) as s
        on s.player_id = p.id
    GROUP
        BY p.id
        , p.player
    ORDER BY points DESC
    LIMIT 0,10

  6. #6
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. So last question. I combined all the players to one table and am now trying to compile records, such as most points in a game, most goals in a game, most assists in a game over all of the season. I have the SQL results I am looking for, the only thing I would like added are the team names. I have succesfully pulled the teamid and the opponent_id, now I just need to look these up in the teams table. Is there anyway I can do both lookups in this statement, or would that be too much and I have to split it up?

    Code:
    SELECT p.id
        , p.player
        , p.mugshot
    , s.team_id
    , s.opponent_id
        , s.goals
        , s.assists
        , s.fouls
        , coalesce(s.assists + s.goals) as points
    FROM league2014 as p
    LEFT OUTER JOIN (SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2014
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2013
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2012
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2011
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2010
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2009
     UNION ALL
     SELECT goals
            , assists
            , fouls
            , player_id
            , team_id
            , opponent_id
        FROM stats2008) as s
        on s.player_id = p.id
    ORDER BY points DESC
    LIMIT 0,10

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, that's fine

    you can have multiple many-to-one relationships in a query with no problem

    each id (teamid and the opponent_id) can join to its respective lookup table, as it will only return one row into the join

    if both of them are joining to the teams table, you still need two joins, using table aliases to distinguish the "copies" of the teams table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •