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;
- A player might have attempted some field goals, but no extra points
- A player might have attempted extra points but no field goals
- 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?