Average Function
Moderator: General Moderators
Average Function
Am am trying to find the average of several fields in a mysql db. I am using this code:
<?php
$result = mysql_query("SELECT * FROM database",$db);
$num_rows = mysql_num_rows($result);
$field1 = mysql_query("SELECT SUM(fieldname) FROM database",$db);
$var1 = $field1 / $num_rows;
echo "$var1";
?>
However, instead of returning the correct average (should be about 16), '0.085714285714286' is what gets returned. Any advice?[/php_man]
<?php
$result = mysql_query("SELECT * FROM database",$db);
$num_rows = mysql_num_rows($result);
$field1 = mysql_query("SELECT SUM(fieldname) FROM database",$db);
$var1 = $field1 / $num_rows;
echo "$var1";
?>
However, instead of returning the correct average (should be about 16), '0.085714285714286' is what gets returned. Any advice?[/php_man]
First of all, it's probably returning unexpected values because you're not operating on the value returned, you're operating on the mysql_object. But let's ignore all that, because it's unnecessary. You can just use:
Code: Select all
SELECT AVG(fieldname) FROM database GROUP BY something-
Illusionist
- Forum Regular
- Posts: 903
- Joined: Mon Jan 12, 2004 9:32 pm
Ok, so I changed SUM to AVG
Now it returns "Resource id #2".
How can I set it to display the numerical average?
Code: Select all
<?php
$field1 = mysql_query("SELECT AVG(fieldname) FROM survey_scores");
echo "$field1";
?>How can I set it to display the numerical average?
-
Illusionist
- Forum Regular
- Posts: 903
- Joined: Mon Jan 12, 2004 9:32 pm
i suggest going back to the basics and/or reading over some MYSQL tutorials. Thats what Unipus was talking about earlier. You can't do that.
try this:
try this:
Code: Select all
<?php
$result = mysql_query("SELECT AVG(fieldname) FROM survey_scores");
echo mysql_result($result,0);
?>Great. But just to clarify, mysql_query doesn't return a value, it returns a MySQL resource object (explains the output you saw). You need to further mine that resource to get the result you're looking for, which is what Illusionist's addendum does... breaks down the resource into an array and returns the first value of the array.