Calculating average from a percent

Question

I am using DW to create record set that calculates the percentage from a table grouped by hospital name. I need to get the average of the percentage (by summation of the percentage divided by the number of hospital). I have only one table.

mysql_select_db($database_localapconnect, $localapconnect);
$query_Recordset1 = "SELECT    COUNT(IF(ap_intervalname = 'Less than or equal to 12 hours', ap_intervalname, NULL))/ COUNT(ap_intervalname) *100 AS 'percent' FROM maintble  GROUP BY `hospitalname`";
$Recordset1 = mysql_query($query_Recordset1, $localapconnect) or die(mysql_error());

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

while($row_Recordset1 = mysql_fetch_array($Recordset1)) {
print_r( $row_Recordset1['percent'].'<br>');    
}

print_r ($totalRows_Recordset1).'<br>' ;


echo  sum($row_Recordset1['percent'])/$totalRows_Recordset1;

result:

I get the following error:

Fatal error: Call to undefined function sum() in C:\wamp\www\ap_database\Untitled-1.php on line 49

http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Strawberry, Dec 06 '14 at 17:11
I think you are using SQL and PHP as if they were interchangeable. They aren't, they are different languages. — Álvaro González, Dec 06 '14 at 17:14
[***sum** doesn't exist. Closest matches:*](http://php.net/manual-lookup.php?pattern=sum&scope=quickref) — Funk Forty Niner, Dec 06 '14 at 17:19

score 1 · Answer 1 · answered Dec 06 '14 at 17:18

The function sum doesn't exist in PHP. If you want the sum of the elements of an array, you should use array_sum

But, in this specific case, I think it's better sum inside the loop, because you are already accessing all the elements anyway...

For example, like this:

$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$sum = 0.0;
while($row_Recordset1 = mysql_fetch_array($Recordset1)) {
    print_r( $row_Recordset1['percent'].'<br>');    
    $sum += $row_Recordset1['percent'];
}


print_r ($totalRows_Recordset1).'<br>' ;

echo  $sum/$totalRows_Recordset1;

score 0 · Answer 2 · answered Dec 06 '14 at 17:15

0

The easiest way is to use a subquery:

SELECT avg(percent)
FROM (SELECT AVG(CASE WHEN ap_intervalname = 'Less than or equal to 12 hours' THEN 100.0
                      ELSE 0 END) AS percent
      FROM maintble
      GROUP BY `hospitalname`
     ) m;

answered Dec 06 '14 at 17:15

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Dec 06 '14 at 17:21

You're calling sum() in this line of php code.

 sum($row_Recordset1['percent'])/$totalRows_Recordset1; /* mistake */

But that doesn't make sense. For one thing, $row_Recordset1['percent'] is just a plain old number. For another thing, php doesn't have a built in sum() function.

If you don't want to use SQL to do that computation, you need to add up the values in your recordset processing loop.

$sum = 0.0;
$count = 0;
while($row_Recordset1 = mysql_fetch_array($Recordset1)) {
    $count ++;
    $sum += $row_Recordset1['percent'];
    print_r( $row_Recordset1['percent'].'<br>');    
}
echo  $sum / $count;

Notice I counted the rows explicitly rather than relying on num_rows(). That's for a good reason; under some circumstances num_rows() is not reliable before you read the recordset.

Thanks for you expert reply, still it doesn't result the average percent. I resorted to hard coding the sum of the denominator since it is more or less a fixed number..SELECT CONCAT(FORMAT(sum(vt)/6,1),'%') AS 'National Average' FROM (select count(`hospitalname`) as cnt, COUNT(IF(ap_intervalname = 'Less than or equal to 12 hours', ap_intervalname, NULL))/ COUNT(ap_intervalname) *100 as vt from maintble WHERE `dischargedt` BETWEEN coldatefrom AND coldateto GROUP by `hospitalname`) a; — walid youssef, Dec 24 '14 at 05:27

Calculating average from a percent

3 Answers3