How to sum values on individual records?

I'm having a hell of a time trying to get this to work and I'm sure it's a relatively simple solution...

I'm managing the stats for my hockey team and in this particular select statement, I'm trying to sort the top point scorers in a single game.

IE, I have 1 player right now who holds positions #1 and #3 for most points in a single game, but my code sums the points from every game he's played in.


SELECT players.PID PID, players.f_Name fName, players.l_Name lName, SUM (player_stats.G + player_stats.A) PTS
FROM players, player_stats
WHERE players.PID = player_stats.PID
AND year = 2005
AND league = 'Spring'
GROUP BY players.PID ORDER BY PTS DESC


How can I modify this so that G and A are summed on only single records, and not all the records?

Remove the SUM, that is for columns, not rows.


note your group by clause should include all columns named in your select statement except the SUM which is an aggregate column. Mysql allows you to do this (other databases would not run the query) but warns you about incorrect results. See the manual on GROUP BY HIDDEN FIELDS for more info.

Lastly, a small item, you don't need to alias your columns as such:
SELECT players.PID PID, players.f_Name fName, players.l_Name lName

when output without those aliases you have they will display as exactly that, the table name does not get appended. In other words you are doing this:
players.PID PID which is the same as this: players.PID as PID which is assigning an alias of the same name as the column name. however leaving the alias off you would still see PID in the column name. So you can change your select to:
SELECT players.PID, players.f_Name, players.l_Name

and save a bit of typing.

you only need aliases when you are going to give a different name to the column, or when joining two or more tables and there are columns with the same name in more than one table.