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?
, 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
, NULL AS fg
, Made AS pa
FROM ExtraPoints ) AS s
JOIN Players AS p
ON p.PlayerID = s.PlayerID
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
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.
oh shoot, the GROUP BY, sorry for forgetting it and good job for spotting that yourself