Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2005
    Posts
    38
    Thanks
    0
    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.

    Code:
    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?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    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:
    Code:
    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:
    Code:
    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.
    Last edited by guelphdad; 09-05-2006 at 01:53 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •