Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
09-05-2006, 06:04 AM #1
- Join Date
- Jan 2005
- Thanked 0 Times in 0 Posts
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
09-05-2006, 02:46 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 148 Times in 139 Posts
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:
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:Code:SELECT players.PID PID, players.f_Name fName, players.l_Name lName
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:
and save a bit of typing.Code:SELECT players.PID, players.f_Name, players.l_Name
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.
Last edited by guelphdad; 09-05-2006 at 02:53 PM.