...

View Full Version : Sorting a SUM



bayken37
01-17-2013, 10:05 PM
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.


$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!

Fou-Lu
01-17-2013, 10:14 PM
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:


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).

Old Pedant
01-17-2013, 10:32 PM
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:


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...


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.

Fou-Lu
01-18-2013, 12:35 AM
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).


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.

bayken37
01-18-2013, 12:38 AM
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. (...)

Fou-Lu
01-18-2013, 12:44 AM
That's easiest done in the language you are working with. In PHP with the mysql library, that would look like so:


$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.


Sorry I missed a part from both the format and the argument list for the position.

Old Pedant
01-18-2013, 12:49 AM
You would just do that in PHP/ASP/JSP code.

In PHP, something like:


$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/>

bayken37
01-18-2013, 01:09 AM
Where would the ++$i or ++$rownum go in the following output attempt? :D


$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>";

Fou-Lu
01-18-2013, 01:53 AM
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):


while($row = mysql_fetch_assoc( $result ))
{
if (0 == ($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.

Old Pedant
01-18-2013, 02:12 AM
Even better:


$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?

Old Pedant
01-18-2013, 02:16 AM
A critique, FouLu, with apologies.

It is true that

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

Because the test is actually done as

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:


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


$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.

Fou-Lu
01-18-2013, 02:39 AM
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.


// I'll keep it as the if:
if (0 == ($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:


$i = 1;
printf("%s",
$i == 1 ? "cat" :
$i == 2 ? "dog" :
$i == 3 ? "mouse" :
"Unknown");

Results in mouse, while using a language with rtl:


int main(int argc, char **argv)
{
int i = 1;
printf("%s",
i == 1 ? "cat" :
i == 2 ? "dog" :
i == 3 ? "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.

Old Pedant
01-18-2013, 02:58 AM
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:


$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:


$i = 1;
while ( ... )
{
$class = ( $i % 2 == 0 ) ? "even" : "odd";
...
++$i;
}

I really don't like seeing stuff like


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".

Old Pedant
01-18-2013, 03:06 AM
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:


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):


for ( $rownum = 1; $rownum > 0; ++$rownum )
{
$row = mysql_fetch_assoc($result) );
if ( $row == false ) break;
...
}

No?

Old Pedant
01-18-2013, 03:08 AM
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.

Fou-Lu
01-18-2013, 04:38 AM
Heheh, yes PHP is probably the most complicated language I've ever used. The, um, quirks I'll call them (ie: inconsistencies :D) are quite nonsensical. I will give it that its extremely easy to learn basic PHP, but if its your first language than it will teach really bad habits.

So things like mysql_fetch_* returning false is perfectly fine for the basic PHP level. Null = false = 0 = '0' = '' = array(), so with the weak datatypes it actually works alright (MySQLi fixes this btw, and returns null when no more records are present). Problem is more into the OO approach for things where testing for null is common place. Unlike the procedural PHP where null is rarely used for any type of condition.
It is frustrating though. Only in PHP do you actually need to use === to see if a 'string' contains 's'. Lol. Even perl returns -1 on no match.

Oh and for the for loop, yeah there is nothing wrong with that. You could also use a while to do both: while (++$i && $row = mysql_fetch_assoc($qry)). Note that if you start $i at 0 (which is what I'd do if I wanted 1 as the first record), and you don't provide a condition for the increment like above, using $i++ won't work of course since its evaluated as 0, which would terminate the loop.
Ahh, good fun.

bayken37
01-18-2013, 11:12 AM
Wow, that's perfect!

I was wondering one last (maybe?) thing. Since this query is looking at just one team that I've specified, is it possible to set up something where depending on the .php url it could automatically look up the team's results.

Say I name this "games.php", is there a way to set it up so that if you go to "games.php?Team_ID=ATL" it would give you ATL and ..?Team_ID=NYY would give you the Yankees and so on? Or would this be too difficult for a rook?

bayken37
01-18-2013, 12:42 PM
One more thing :D

After playing around with what you guys have helped me with I decided to get bold and try a division function to calculate career AVG (hits divided by at bats). When I run the following query in my SQL I get what I want, but when I do this with the PHP, I get the correct order and players, but the AVG column returns all zeros.


echo '<td valign="top" align="left" width="250">';

$sql = 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
. ' FROM retired_batters '
. ' WHERE Team = \'ATL\' '
. ' GROUP BY Pos, FName, LName'
. ' ORDER BY AVG DESC '
. ' LIMIT 10';

$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' align='right'>AVG</td></tr>";

$i = 0;

while($row = mysql_fetch_assoc( $result ))
{
if (0 == ($i++ & 1))
{
// this is odd
$sClass = 'odd';
}
else
{
$sClass = 'even';
}
printf('<tr class="%s">
<td align="right">%d.</td>
<td>%s %s</td>
<td align="right">%d</td>
</tr>' . PHP_EOL, $sClass, $i, $row['FName'], $row['LName'], $row['AVG']);
}

echo "</table>";

echo '</td>';

Fou-Lu
01-18-2013, 03:29 PM
You have two questions here.
The first is by fetching values by get. Yes that would be trivial to do; you simply need to check if your provided value is available, and if so, you then go ahead and fetch it. That's simply a matter of structure like so:


if (isse($_GET['Team_ID']))
{
// All of this code you have now goes here.
if (get_magic_quotes_gpc())
{
$_GET['Team_ID'] = stripslashes($_GET['Team_ID']);
}
$team_id = mysql_real_escape_string($_GET['Team_ID']);
$sql = 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
. ' FROM retired_batters '
. ' WHERE Team = \'' . $team_id . '\' '
. ' GROUP BY Pos, FName, LName'
. ' ORDER BY AVG DESC '
. ' LIMIT 10';
// continue with the rest of your code
}



I don't know what kind of results that AVG would be, but what you describe to what is currently in the code would indicate that AVG would be a fractional value: AVG=0.885 for example.
In the printf code:


printf('<tr class="%s">
<td align="right">%d.</td>
<td>%s %s</td>
<td align="right">%d</td>', ...);

Those replacements are %s = string, and %d = integer. If you have 0.885 for example, casting that to an integer would result in 0. What you do is use %f to indicate that it is a double value instead, and you can autoround it with the modifiers. So instead of %d you can use %0.3f which would show three significant digits and the 0.
That is a guess as to what the issue is since it sounds like the AVG pulls the correct results, its simply display as int.
This is one reason why printf is often not liked by people, but I personally feel that the ability to modify the output structure and perform argument location swapping trumps that of accidentally providing the incorrect datatype.

bayken37
01-18-2013, 04:20 PM
The second part is perfect, however the first part I'm getting the following: "Fatal error: Call to undefined function isse()"

Fou-Lu
01-18-2013, 04:34 PM
Typo on my part, the function is isset with a 't' at the end.

bayken37
01-18-2013, 04:51 PM
Woohoo!

What if I wanted to only show those players who had GREATER THAN a certain number of the SUM(AB), say 100. I've been messing around with that in vain on MySQL but can't quite choose the right thing.

Sorry to keep bombarding you with questions but I really appreciate it!

Fou-Lu
01-18-2013, 04:57 PM
That requires an aggregate HAVING clause:


$sql = 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
. ' FROM retired_batters '
. ' WHERE Team = \'' . $team_id . '\' '
. ' GROUP BY Pos, FName, LName'
. ' HAVING SUM(AB) > 100'
. ' ORDER BY AVG DESC '
. ' LIMIT 10';

If you use any aggregation (ie: group by on a property and looking for something like a COUNT, SUM, etc), then you always compare it using the HAVING clause. The structure is always: SELECT fields FROM location WHERE where GROUP BY groupby HAVING having ORDER BY order, so you stick having between the group by and order by.

bayken37
01-18-2013, 05:19 PM
using the isset to get the Team_ID what do I need to do to get the tag for something like an img

like:


<img src="\'' . $team_id . '\'.png">

What I put in was obviously wrong, but what do I need to change that to to get it to say ATL.png for Team_ID=ATL?

Fou-Lu
01-18-2013, 05:55 PM
You don't wrap it in quotes like that as if that would parse it would be <img src="'ALT'.png"/> which isn't correct. You simply use: <?php echo '<img src="' . $team_id . '.png" alt=""/>';. It must be echoed through PHP if you want to parse a variable.
If you do this though, I'd suggest not using the $team_id and using a second variable, or not running $team_id through mysql_real_escape_string until its used within the query. Then you should use urlencode on the $team_id within the img tag in case it contains characters that need to be interpreted differently such as a space.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum