how to average numbers in a table column

Anon

I have a INT field in a MySQL dbase that I want to obtain the average of all the selected numbers within that column, how can I do this?

Anon

Easy as pi. 🙂

$query="select AVG(columnname) from tablename";
$result = mysql_query($query,$DATABASE);
$row = mysql_fetch_array($result);

$average = $row["AVG(score)"];

MySQL contains a bunch of usful math functions for all kinds of things, if you do a search on the MySQL doumentation for the string "AVG()" you should find the other commands all nearby 🙂

Hope this solves your problem.

Anon

thanks for the help. I did the lookup on MySQL before posting but couldn't figure out how to intergrate it into the PHP.

I tried your suggestion above as following:'

$result6 = "SELECT AVG(rating) FROM Rreview WHERE model='$model'";
$query = mysql_query($result6,$db);
$row = mysql_fetch_array($query);
$average = $row[rating];
echo $average;

and I am not getting anything back out (no errors or numbers). I KNOW the column 'rating' is occupied with data for that model. I even tried removing the WHERE statement and still get nothing. The column type is INT with default of ).

Is my code right? Other suggestions? Thanks for the quick response!!!

/frank

Anon

Yep, simple fix for that...

The line $average = $row[rating]; should read: $average = $row["AVG(rating)"];

The return column name is actualy the exact string that you used in the select part of the statement.

Examples:

select test1,test2,test3,sum(test1) from tablename

then you would have resulted in the following avaliable values in your $row variable.

$row["test1"]
$row["test2"]
$row["test3"]
$row["sum(test1)"]

Easy ehe... you wouldn't belive how long it took me to find a simple tutorial to show me how this worked when I had to learn it for the first time 🙂

Anon

how do I format the results. I continue receiving output with the answer and 4 zeros after the decimal.