Average Query Results

[reefland]

Ok, here's another one from the noob. I am trying to run a query on a column that contains integers and return the average from that query. Obviously the first step is to sum the results and then divide it by the number of rows returned, but I am having some troubles.

$query1 = mysql_query("SELECT results FROM test WHERE foo='blah' AND fooblah='blahfoo'");
$list = mysql_num_rows($query1);
$row1 = mysql_fetch_array($query1);
$totalresults = array_sum($row1) / $list;
echo "$totalreults";

Thanks,
Scott Z.

[merk]

$query = "SELECT average(column) AS columnaverage FROM test WHERE foo='blah' AND fooblah='blahfoo'";
....

Reference $result[columnaverage]

[reefland]

Hey merk!

I appreciate your help, however I had no luck.

$query = "SELECT average(results) AS resultsaverage FROM test WHERE userid='$User[userid]' AND test_type='$testtype'";
$totalaverage = $result["resultsaverage"];
echo "$totalaverage";

Thoughts?
Scott Z.

[merk]

You have to use the relevant mysql fetching functions.


$connection=mysqlconnect();
$query=mysqlquery($querytxt, $connection);
$array=mysql_fetch_result($query);

echo $array[averagecolumn];


The bold part might not be totally correct.

[TiLaser]

It's avg() not average()

[reefland]

Thanks merk and TiLaser, however I still have issues.

First of all, we are connecting to the database, I am just omitting that from my code. The query works in phpmyadmin but I can't get it to work in my code. Here is what I have now:

$query = mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='1' AND test_type='Calcium'");
$array = mysql_fetch_result($query);
$totalaverage = $array[columnaverage];
echo "$totalaverage";

I get a Fatal Error, call to undefined function mysql_fetch_result. If I try mysql_fetch_array() it's an invalid resource.

Thanks again for your help,
Scott Z.

[merk]

Thanks merk and TiLaser, however I still have issues.

First of all, we are connecting to the database, I am just omitting that from my code. The query works in phpmyadmin but I can't get it to work in my code. Here is what I have now:

$query = mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='1' AND test_type='Calcium'");
$array = mysql_fetch_result($query);
$totalaverage = $array[columnaverage];
echo "$totalaverage";

I get a Fatal Error, call to undefined function mysql_fetch_result. If I try mysql_fetch_array() it's an invalid resource.

Thanks again for your help,
Scott Z.
Use mysql_fetch_row()

[reefland]

Use mysql_fetch_row()
Thanks merk and beleive it or not but I finally got it! :)

$query = mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='$User[userid]' AND test_type='$testtype'");
$row = mysql_fetch_array($query);
$totalresults = $row["columnaverage"];
echo "$totalresults";

I don't know why I had so many troubles with that but I appreciate your continued help!

Now, here is a follow-up question. The results of the query display 418.333333, how can I limit this to only show the hundreths (only 2 spots after the decimal)?

Thanks a million for helping out this beginner,
Scott Z.

[merk]

I didnt help you with the mysql functions because you didnt ask for that. mysql_fetch_row is a better fuction to do what you want to do.

And of course, for rounding i hope you checked out www.php.net/round (http://www.php.net/round) before asking :)

[reefland]

Hey merk!

Yeah I looked at php.net at round and actual found that using number_format() is probably a better way to go for me because in some cases, I want 400.00 to be shown and number_format() allows me to set the number of numbers after the decimal.

Thanks again for your help!
Scott Z.