#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Paris, France
    Posts
    15
    Rep Power
    0

    Sum and Group By


    Hello everyone,

    Quite new to SQL, but trying to make some progress for a baseball-related site of mine.

    For instance from my tables I want to produce a query that shows the career leaders for a given stat (Games Played in this case) for the team that I specify.

    PHP Code:
    $query "SELECT Pos, FName, LName, SUM(G) FROM retired_batters WHERE Team = 'ATL' GROUP BY LName"
         
    $result mysql_query($query) or die(mysql_error());

    while(
    $row mysql_fetch_array($result)){
        echo 
    $row['Pos']. " "$row['FName']. " "$row['LName']. " "$row['SUM(G)'];
        echo 
    "<br />";

    When I run this I get the info that I'm looking for: Position he played, First name, Last Name, Games he played for ATL (and it's obviously sorted by last name.

    However, I want this data to be sorted by the sum (i.e. I want to know the top 25 for this stat) but when I try to GROUP BY or ORDER BY I do something wrong and get weird results.

    What am I doing wrong and how could I limit the results to only the top 25?

    Thanks in advance!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT Pos
         , FName
         , LName
         , SUM(G) AS games
      FROM retired_batters 
     WHERE Team = 'ATL' 
    GROUP 
        BY LName
    ORDER
        BY games  DESC LIMIT 25
    note if you ever have two players with the same last name on a team, this GROUP BY will give you problems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo