ordering after sum and group by

bigtall

I have a database full of yearly player baseball stats and this table in particular deals with just hitting stats. The code below is what I have to sum the number of games each player has played and what I want to do is get the player that has played the most. I'm going to do this with every field to get career leaders, but i'm starting small right now 😃 . The player's first and last name are in two separate columns so i've selected both as well as the games played column named "g". The sum(g) works fine and every player has the appropriate carrer number of games played, but when It gets to the ordering part the fields are in no order at all as I've proven to myself with the foreach() statement to look at the entire array so whatever I pull out as the "apparent" leader after the ORDER BY clause is incorrect. What am I not doing here? Any help is much appreciated.

$sumgames = mysql_query("SELECT lastname,firstname,sum(g) FROM hitting GROUP BY lastname,firstname ORDER BY g");

for ($x=0; $x<mysql_num_rows($sumgames); $x++)
{

 $careergame = mysql_fetch_assoc($sumgames);

 foreach($careergame as $val)
 {
 echo $val;
 echo "<br>";
 }
}

Sxooter

order by sum(g)

bigtall

I tried that and I get this:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

🙁

Lars_Berg

$sumgames = mysql_query("SELECT lastname,firstname,sum(g) as s FROM hitting GROUP BY lastname,firstname ORDER BY s desc");

bigtall

Aha! Thanks, it works perfectly now!

Sxooter

Is there a reason order by sum(field) doesn't work here? It's always worked for me.

bigtall

And actually another related question to the resulting SELECT statement. I have fields for a baseball player's batting average and a few other percentages that will be displayed incorrectly by a sum() since they are calculated fields. Is there a way to alter the SELECT statement to have the hits field be divided by the at bats field (to take care of the batting average for starters)? I have to sum each of those two fields separately, but have no clue how to divide the two of them. Also i could probably use the AS clause to store that resultant number for sorting like we did above right?

Sxooter

Something like

sum(field1)/sum(field2) should work, or is that not what you're trying to do?

bigtall

Thanks, I figured it was something simple like that but at this point my brain is fried once more :p

bigtall

One more silly question. The sum(field1)/sum(field2) works, but the result only give me 2 numbers after the decimal point. Is that due to how i've defined the hits and at bat field, which is INT(3) for each in the table, or is it something to do with the division of the two sums?

EDIT: :rolleyes: if i add a number to the equation it works...so sum(field*'1')/sum(field). No clue, but it makes me laugh nonetheless.

Sxooter

Well, I just tried it in MySQL, and whether they are int(3) or just ints, I get 3.33 from this:

create table test (i1 int, i2 int);
insert into test values (10,3);
select sum(i1)/sum(i2) from test;

3.33

In Postgresql 7.4 I get 3.

Note that I'm pretty sure that the ANSI standard says you should get 3, since the types of the two inputs are both ints the result should be as well.

Note that select 10/6 gives and answer of just 1 in postgresql, and 1.67 in MySQL 3.23.41.

Making it into select '10'/6 I get 1.6666667

In postgresql I still get just 1. I have to explicitly cast it to float, something I can't do in the older MySQL I'm running to test here.

select 10::float/6 OR select cast(10 as float)/6 both give 1.66666666667 or so.

Lars_Berg

Note that I'm pretty sure that the ANSI standard says you should get 3

You are correct.

Sxooter

Thanks for the confirm.

It's nice having a database that at least tries to follow the standard... 😉