How do I sum all of my MySQL integer results?

[DaveLinger]

So I have a bunch of rows, and for each row, the column "rating" has a number from 1-5. I need to add all of these together, so like if there were 3 rows with 5 for rating, it would return 15. If it's possible to do it with an SQL statement, I'll like it to average them.

[MemphiS]

mysql_query("SELECT SUM(rating) FROM `rate`");

[HaLo2FrEeEk]

That'll get the sum, then you need to do:

 

mysql_num_rows(mysql_query("SELECT * FROM `rate`"));

 

and divide the total given by memphis' query by the number given by mine.

[Yesideez]

$total=mysql_fetch_assoc(mysql_query("SELECT SUM(rating) FROM `rate`"));
$count=mysql_fetch_assoc(mysql_query("SELECT COUNT(*) FROM `rate`"));
echo 'Total is '.$total[0].' and average is '.$total[0]/$count[0];

[craygo]

Rather than doing 2 queries you can always use the number of rows returned from the query

 

<?php
$sql = "SELECT SUM(rating) AS rate_sum FROM rate";
$result = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($result);
$row = mysql_fetch_assoc($result);
extract($row);
echo "Total is ".$rate_sum." and average is ".$rate_sum/$num_rows;
?>

 

Ray

[DaveLinger]

I ended up going with something like "SELECT *, AVG(rating) as average from table"

 

That gave me exactly what I needed.

 

Thanks for your replies, very helpful.