Calculating average value from table column

sum, average, php, calculate values, divide

[borisz]

Hi everyone,

can anyone help me with fallowing:

I have fallowing column (CommentRate) inside cd_comments table

and I have fallowing values e.g.: 3, 5, 4, 4 and I need to calculate average value from those 4 member ratings.

I already made a query which counts the total number of ratings ($totalRows_Comments) which I will use to divide it with Sum of all values, which should give me the average value for the product? so how do I write a query to SUM all those 4 values and then divide them with ($totalRows_Comments), so I would use fallowing to calculate average:


$AverageValue = $E.G.SUMMOFALL / $totalRows_Comments

thanks!

[topr8]

why don't you use AVG in the SQL

it's a built in function to calculate the average

[borisz]

I tryed with this:

$colname_DealRatingAvg = "-1";
if (isset($_GET['ProductID'])) {
 $colname_DealRatingAvg = $_GET['ProductID'];
}
mysql_select_db($database_coupons_database, $coupons_database);
$query_DealRatingAvg = sprintf("SELECT avg(CommentRate) FROM cd_comments WHERE CommentCouponID = %s", GetSQLValueString($colname_DealRatingAvg, "int"));
$DealRatingAvg = mysql_query($query_DealRatingAvg, $coupons_database) or die(mysql_error());
$row_DealRatingAvg = mysql_fetch_assoc($DealRatingAvg);
$totalRows_DealRatingAvg = mysql_num_rows($DealRatingAvg);

and when I echo and preview the page:

<?php echo $DealRatingAvg ?> 

I get fallowing text "Included Resource id #18" and not average value.

[topr8]

when using avg you need to use 'Group By' in your query

SELECT avg(CommentRate) FROM cd_comments WHERE CommentCouponID = %s GROUP BY CommentRate

[borisz]

well even when I add GROUP BY CommentRate I still get "Included Resource id #18" as output value but not a real Average value

[coopster]

You need to echo the column index of the returned fetch. When I use group/aggregate functions I use a column alias for ease of use.

SELECT avg(CommentRate) AS DealRatingAvg ... 
... // then when retrieving the fetched assoc row you need to use the index 
$row_DealRatingAvg = mysql_fetch_assoc($DealRatingAvg); 
echo $row_DealRatingAvg['DealRatingAvg']; 

See the online PHP manual for mysql function usage and examples.

[borisz]

thanks all for help, its working now