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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Sorting a SUM

  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sorting a SUM

    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
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    OldPedant would know for sure offhand, but I'm pretty sure MySQL allows you to order by an alias (I know I've tested this time and again but I can never remember which DBMS does what :P). So its very easy:
    Code:
    SELECT Pos, FName, LName, SUM(G) AS SumOfG FROM retired_batters WHERE Team = 'ATL' GROUP BY LName ORDER BY SumOfG DESC LIMIT 25
    Looks like it would work. Ordering by SUM(G) should work as well (if you can't use an alias, but I'm pretty sure MySQL does).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Technically, you should *ALWAYS* make sure you GROUP BY *ALL* fields in the SELECT list that are not aggregate functions.

    As Fou-Lu gave this:
    Code:
    SELECT Pos, FName, LName, SUM(G) AS SumOfG 
    FROM retired_batters 
    WHERE Team = 'ATL' 
    GROUP BY LName 
    ORDER BY SumOfG DESC 
    LIMIT 25
    If you had, for example, 3 different players with the last name of "JONES", then all three would be LUMPED TOGETHER because the GROUP BY specified *only* LName !!

    Most database won't even allow the query as written there. MySQL does, but then it has funky rules about what using an incomplete GROUP BY means.

    So...
    Code:
    SELECT Pos, FName, LName, SUM(G) AS SumOfG 
    FROM retired_batters 
    WHERE Team = 'ATL' 
    GROUP BY Pos, FName, LName
    ORDER BY SumOfG DESC 
    LIMIT 25
    Note how the SELECTed and GROUPed fields match.

    By the by, you'd still be in trouble if you had two players who played the same position and had the same first and last names. Unsual, but not impossible.

    p.s.: *ALL* DBs that I know of allow aliased column names in the ORDER BY clause. MySQL is unique in that it also allows them in the GROUP BY clause.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I've been doing *far* to much work in (sadly) Access O.o
    Using the builders I kinda forget about the group by on all fields. Access for sure doesn't let you group by an alias.
    *sigh* I wish I could put my scheduling in SQLServer.
    I think oracle still rejects groupby on alias? Of course you can ways get around it by selecting a subquery, but its not quite the same. The newer ones may (I use 9g with veeerrrryyyy little actual manual queries; I wrote the sp's awhile ago, and none of them are aggregate).

    Edit:
    Ah yes sorry I see what you mean. I'm probably thinking of the group by, not the order by. It makes sense to be able to order by any field regardless of the alias.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks, worked perfectly! Exactly what I was looking for.

    One more question on this issue if I can be so bold..

    So now that I have the top 25 I'm looking for, how can I go about labeling each row according to the ranking when it prints out the records.

    I'm sure this is put in the loop somewhere, I just need a little help getting there.

    So I would want it to look like:
    1. PLAYER X, POS (Stat)
    2. PLAYER Y, POS (Stat)
    3. (...)

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    That's easiest done in the language you are working with. In PHP with the mysql library, that would look like so:
    PHP Code:
    $i 0;
    while(
    $row mysql_fetch_assoc($result))

        
    printf('%d. %s %s, %s (%d)<br />' PHP_EOL, ++$i$row['FName'], $row['LName'], $row['Pos'], $row['SumOfG']);

    The $i is the important one there. You can format it as you see fit, probably using a table or some nice spans, but that's just a basic printf output.

    Edit:
    Sorry I missed a part from both the format and the argument list for the position.
    Last edited by Fou-Lu; 01-18-2013 at 12:48 AM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    You would just do that in PHP/ASP/JSP code.

    In PHP, something like:
    Code:
    $sql = "...that query we showed...";
    $result = mysql_query( $sql ) or die( mysql_error( ) );
    $rownum = 0;
    while ( $row = mysql_fetch_array( $result ) )
    {
        ++$rownum;
        $fname = $row["fname"];
        $lname = $row["lname"];
        $pos = $row["pos"];
        $games = $row["SumOfG"];
    
        echo "<tr><td>$rownum</td><td>$lname, $fname</td><td>$pos</td><td>$games</td></tr>\n";
    }
    Or something like that. I don't use PHP, so I have to kind of wing it.

    *********

    EDIT: Too slow. But hey, I stuck my output into a nice <table> instead of into ugly unformatted rows. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Where would the ++$i or ++$rownum go in the following output attempt?

    PHP Code:
    $result mysql_query($sql) or die(mysql_error());


    echo 
    "<table border='0'cellpadding='2' cellspacing='1' width='250'>";
    echo 
    "<tr class='header'> <td width='15'>Rk</td> <td width='210'>Player</td> <td width='25'>Games</td></tr>";

    while(
    $row mysql_fetch_array$result )) 
    {
    if(
    $color==1)
    {
            echo 
    "<tr><td class='odd'>";
            echo 
    RANKNUMBER
        
    echo "</td><td class='odd'>"
        echo 
    $row['FName']. " "$row['LName'];
        echo 
    "</td><td class='odd' align='right'>"
        echo 
    $row['SumOfG'];
        echo 
    "</td></tr>"
    $color="2";

    else
    {
            echo 
    "<tr><td class='even'>";
            echo 
    RANKNUMBER
        
    echo "</td><td class='even'>"
        echo 
    $row['FName']. " "$row['LName'];
        echo 
    "</td><td class='even' align='right'>"
        echo 
    $row['SumOfG'];
        echo 
    "</td></tr>"
    $color="1";
    }
    }

    echo 
    "</table>"

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Oh don't use the if/else like that. It looks like too much code. It can be much simpler. To answer your question, it would go in where you have RANKNUMBER. You can't use RANKNUMBER here since that would represent a constant in php (ie: unchangeable), and failing that it would become a string (which I hate that PHP does that). Effectively, its the $colour number, but instead you would keep going (I'll show you how to do that in one variable):
    PHP Code:
    while($row mysql_fetch_assoc$result ))  
    {
        if (
    == ($i++ & 1))
        {
            
    // this is odd
            
    $sClass 'odd';
        }
        else
        {
            
    $sClass 'even';
        }
        
    printf('<tr class="%s">
            <td>%d.</td>
            <td>%s %s</td>
            <td>%d</td>
            </tr>' 
    PHP_EOL$sClass$i$row['FName'], $row['LName'], $row['SumOfG']);

    You should be able to apply your class to the table's tr css, and cascade it to the td from there.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Even better:
    Code:
    $i = 0;
    while($row = mysql_fetch_assoc( $result ))  
    {
        printf("<tr class=\"%s\"><td>%d.</td><td>%s %s</td><td>%d</td></tr>\n",
               ( (++$i & 1) == 0 ? "even" : "odd" ),
               $i, $row['FName'], $row['LName'], $row['SumOfG']
              );
    }
    Why create that unneeded $class variable?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    A critique, FouLu, with apologies.

    It is true that
    Code:
        if (0 == ($i++ & 1))
        {
            // this is odd
            $sClass = 'odd';
        } ...
    works.

    Because the test is actually done as
    Code:
        0 == ($i & 1 )
    and then, after the text, $i is incremented.

    So when $i is even, that expression returns true. But because of the POST-INCREMENT, then by the time we get to the assignment to $class, indeed $i is odd.

    So it works.

    But it's clear as mud.

    I *MUCH* prefer:
    Code:
        if (0 != (++$i & 1))
        {
            // this is odd
            $sClass = 'odd';
        }...
    Because here it is clear we are FIRST bumping the value of $i and then testing that new value and, indeed, that new value agrees with the even/odd sense we get.

    Of course, I really prefer it all as a ternary operation, as shown in my post, but even when not a ternary operator I think the PRE-INCREMENT makes the operation clearer.

    Or do you not think so?

    I also think it's a tiny bit clearer to do
    Code:
       $class = ( ++$i % 2 == 0 ) ? "even" : "odd";
    because most people understand the concept of modulo better than bitwise ANDing. But I'll grant you that probably save 2 nanoseconds using the bitwise and.
    Last edited by Old Pedant; 01-18-2013 at 02:19 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    To me, both of those are simply the same. It doesn't make a difference between the post or the pre operation on it. I see your point though, some people will read it easier seeing that $i is 1 and then checked versus $i is 0, checked and then incremented.

    Even clearer though would be to simply not use the increment (either pre nor post) during the if or ternary, and apply it instead to the $i directly when displaying.
    PHP Code:
    // I'll keep it as the if:
    if (== ($i 1))
    {
        
    // even    
    }
    else
    {
        
    // odd
    }
    // later
    print ++$i
    Ternary would be my approach as well; however, I typically limit the ternaries when I can while posting examples simply due to PHP's ltr evaluation of them. Its only a problem when nesting them, but it really is a pain:
    PHP Code:
    $i 1;
    printf("%s",
        
    $i == "cat" :
        
    $i == "dog" :
        
    $i == "mouse" :
        
    "Unknown"); 
    Results in mouse, while using a language with rtl:
    PHP Code:
    int main(int argcchar **argv)
    {
        
    int i 1;
        
    printf("%s"
            
    == "cat" :
            
    == "dog" :
            
    == "mouse" :
            
    "Unknown");
        return 
    0;

    Results in (what I consider to be correct) 'cat'. I remember a thread not all that long ago for PHP that actually had an issue in the ternary of just a single level in the echo, which was caused by the concatenation and ternary together. But I can't quite recall what it was exactly.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I agree, certainly, that for clarity it's better to move the increment away from the even/odd test.

    Though then I would do it like this:
    Code:
    $i = 0;
    while ( ... )
    {
        ++$i;
        $class = ( $i % 2 == 0 ) ? "even" : "odd";
        ...
    }
    that is, if you want row 1 to be row 1, make it so throughout the loop except for the point of incementing. I'd also be happy with:
    Code:
    $i = 1;
    while ( ... )
    {
        $class = ( $i % 2 == 0 ) ? "even" : "odd";
        ...
        ++$i;
    }
    I really don't like seeing stuff like
    Code:
    print ++$i;
    because now the test you made on $i earlier doesn't match the value displayed.

    ***********

    That's really weird about PHP's nested ternary operations.

    I can't even parse that to make sense and get "mouse" as the answer!!

    That is, I can't see how to write that in, say, JS code and put parentheses into it in such a way to get "mouse".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    In languages that don't use PHP's funky mysql_fetch_assoc stuff, I actually prefer using a for loop.

    Could do it even with PHP/mysql_fetch_assoc:
    Code:
    for ( $rownum = 1; ( $row = mysql_fetch_assoc($result) ) != false; ++$rownum )
    {
        ...
    }
    No? Nobody ever said you *MUST* use the test of the incrementing variable as the second expression in the for did they?

    Or, if that doesn't work (it should, but PHP is funny):
    Code:
    for ( $rownum = 1; $rownum > 0; ++$rownum )
    {
        $row = mysql_fetch_assoc($result) );
        if ( $row == false ) break; 
        ...
    }
    No?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    And ONLY PHP would return a row if fetch_assoc can get one but false if it can't.

    UGH UGH UGH.

    Any self respecting language would return null instead of false, since an object reference being null makes sense, but a reference being false just cries of inconsistency.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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