Page 1 of 2

Using AVG function

Posted: Wed Mar 31, 2004 7:26 am
by warriors2003
Hey guys using the avg function in some code And need to adjust it. Making a php script for baseball averages. I need to change the Average function, so the math in it is hits divided by ab or [h]/[ab]. Right now I just have the avgAVG function which works but won;t be accurate cause it takes all players into account even if they have no average....here is the code part

-----------------------------
$result = mysql_query ("SELECT round(AVG(avg), 3) as AVG[avg], SUM(ab) as abSum, SUM(h) as hSum, SUM(hr) as hrSum, SUM(rbi) as rbiSum, SUM(r) as rSum, SUM(doubles) as doublesSum, SUM(triples) as triplesSum, SUM(sb) as sbSum from hit WHERE team='Ana'");

--------------------------------
I need the round still in there in the beginning part of the query, but i need to get the avg(avg) out and replace it with the actual formula, h divided by at bats. Can anyone give me a tip on how to do this math function???

THANKS AGAIN!!!!

you guys even helped with the rounding and sums in the beggining thanks again!!!!!!!!!!!!!!!

Posted: Wed Mar 31, 2004 8:08 am
by warriors2003
ooops,,should have added, looking more specifcally of how to code the Sum of [h] divided by the Sum of [Ab] in a sql statement. I know how to do one function now, just don;t know how to combine two in the same math sequence.

Thanks in Advance

Posted: Wed Mar 31, 2004 11:12 am
by Illusionist
why dont you just get the sum of h and Ab, and then do it yourself:

Code: Select all

$h = $row['hSum'];
$ab = $row['abSum'];
echo$h/$ab;

Posted: Wed Mar 31, 2004 4:41 pm
by warriors2003
Ya that looks like the easier way to go,,,but how do I incorporate that, into the ehco I already have???

echo "<tr bgcolor=\"$color\"><td><b>$row[avgAvg]</b></td><td><b>$row[hSum]</b></td><td><b>$row[abSum]</b></td><td><b>$row[hrSum]</b></td><td><b>$row[rbiSum]</b></td><td><b>$row[rSum]</b></td><td><b>$row[doublesSum]</b></td><td><b>$row[triplesSum]</b></td><td><b>$row[sbSum]</b></td></tr>";


Thanks in Advance!!!

Posted: Wed Mar 31, 2004 4:45 pm
by warriors2003
<td><b>$row[hSum/abSum]</b></td>

I tried that but it doesn;t work

Posted: Wed Mar 31, 2004 4:46 pm
by tim
uhh, why make make a new echo? or use <br> tags to seperate them?

8)

Posted: Wed Mar 31, 2004 4:48 pm
by warriors2003
Not quite following you tim,,i want trying to calculate the average more precisely by taking the total hits then dividing by total AB's. I figure they have to go in the same echo???

Posted: Wed Mar 31, 2004 4:51 pm
by tim
warriors2003 wrote:<td><b>$row[hSum/abSum]</b></td>

I tried that but it doesn;t work
When you use the fetch_array function, aloing with signing the values of each row to variables, ie: $row, you can only use one column name.

do you have a column named hSum/abSum? I doubt it. Ill spelled it out for you. do your part and try to learn n examine the code.

Code: Select all

<?php
$h = $row['hSum']; 
$ab = $row['abSum']; 
echo$h/$ab; 

?>

Posted: Wed Mar 31, 2004 4:53 pm
by tim
warriors2003 wrote:Not quite following you tim,,i want trying to calculate the average more precisely by taking the total hits then dividing by total AB's. I figure they have to go in the same echo???
Your way off base and confused.

an echo only outputs something, you can have as many as u PLEASE.

echo "hi";
echo "tim";
echo "your the best ever, joking";

hope that clears it up for you

Posted: Wed Mar 31, 2004 4:54 pm
by warriors2003
I'm kinda learning as I go, so i know their might be some simple things I miss or don;t know how to do. I'll try some more things you guys suggested and see if i can get them to work. Thanks again.

Posted: Wed Mar 31, 2004 4:56 pm
by warriors2003
ya I get what you are saying with the echos,,just thought I might be able to wing something inside the row echo to do the math, I think I stated what i was trying to say wrong to so that didn't help my case :oops:

i;m gonna go back to the lab and see if I can swing this!!! Thanks again!

Posted: Wed Mar 31, 2004 4:58 pm
by tim
add a <br> in your 'row echo' and paste the code that ill wrote for you. it'll work =]

Posted: Wed Mar 31, 2004 5:10 pm
by warriors2003
really sorry for saying this cause I'll sound real stupid,,, but I don't know where to put Ill's code or where the br goes ...my bad.... :oops:

Posted: Wed Mar 31, 2004 5:20 pm
by tim

Code: Select all

<?php
$h = $row['hSum']; // ills code
$ab = $row['abSum']; 
//this can be anywhere on the page after the mysql_fetch_array cause the fetch_array helps point the row grabbing

// the code you have:
echo "<tr bgcolor="$color"><td><b>$row[avgAvg]</b></td><td><b>$row[hSum]</b></td><td><b>$row[abSum]</b></td><td><b>$row[hrSum]</b></td><td><b>$row[rbiSum]</b></td><td><b>$row[rSum]</b></td><td><b>$row[doublesSum]</b></td><td><b>$row[triplesSum]</b></td><td><b>$row[sbSum]</b></td>";  // ends your echo, but not your table
echo "<td><b>$h/$ab; </b></td></tr>"; // the new echo that  incorperates in your table with ills variable.
?>
or all one echo:

Code: Select all

<?php
echo "<tr bgcolor="$color"><td><b>$row[avgAvg]</b></td><td><b>$row[hSum]</b></td><td><b>$row[abSum]</b></td><td><b>$row[hrSum]</b></td><td><b>$row[rbiSum]</b></td><td><b>$row[rSum]</b></td><td><b>$row[doublesSum]</b></td><td><b>$row[triplesSum]</b></td><td><b>$row[sbSum]</b></td><td><b>$h/$ab </b></td></tr>";
?>
i'm sorry, if u cant understand that, I give up =[

Posted: Wed Mar 31, 2004 5:44 pm
by warriors2003
I really really appreciate you trying to help. hehehe,,thanks for making it easy to just copy and paste too!!!..

.....Unfortunately I tried both,,,,and it won;t work, it just displays the / that division sign thats it, so something is funky in the echo. it's my problem tho, not yours, so don;t sweat it!!!..Thanks alot bro!!!!