American Football query

Databases
1

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?

2 
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

3

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.

4

oh shoot, the GROUP BY, sorry for forgetting it and good job for spotting that yourself