SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    American Football query

    As it is the Super Bowl this weekend, here is a topical problem.

    I have a database of American Football stats. As an example, the table storing Field Goals is like this;

    PlayerID, Date, Attempts, Made, Longest

    and the table Extra Points is like this;

    PlayerID, Date, Attempts, Made

    I have another table listing all the players in the league;

    PlayerID, FirstName, LastName

    Stats are collected at the end of each game. If a player attempted at least one field goal in the game, they get a record in the table. Likewise, if they attempted an Extra Point they get a record.

    I wish to calculate which player has scored the most points from kicks - 3 points for every field goal, one point for each Extra Point. Performing simple joins won't work, as;

    1. A player might have attempted some field goals, but no extra points
    2. A player might have attempted extra points but no field goals
    3. The vast majority of players won't have attempted either

    Joining the players table to the Field Goals and the Extra Points tables would seem to be the way forward, but it seems very wasteful as probably 52 out of every 53 players on a roster won't have attempted any kicks at all.

    Could anyone help with an elegant way forward?
    There's more to me than meets the eye, but not much more.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT s.PlayerID
         , p.FirstName
         , p.LastName
         , SUM(s.fg) AS points_from_field_goals
         , SUM(s.pa) AS points_from_pats
         , SUM(s.fg) +
           SUM(s.pa) AS total_points
      FROM ( SELECT PlayerID
                  , Made * 3 AS fg
                  , NULL AS pa
               FROM FieldGoals
             UNION ALL   
             SELECT PlayerID
                  , NULL AS fg
                  , Made AS pa
               FROM ExtraPoints ) AS s
    INNER
      JOIN Players AS p
        ON p.PlayerID = s.PlayerID
    ORDER
        BY total_points DESC LIMIT 1
    note that LIMIT 1 will show only one player, even if there are multiple players with the same highest total point count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Rudy,

    It is the "UNION ALL" bit that makes it all work! I just needed to add a "GROUP BY" clause to your SQL and everything worked.
    There's more to me than meets the eye, but not much more.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh shoot, the GROUP BY, sorry for forgetting it and good job for spotting that yourself
    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
  •