View Full Version : array_sum and average from a foreach

05-26-2011, 03:34 PM
I have an array of dates ($dateMonthYearArr):

Array ( [0] => 04.04.2011 [1] => 04.05.2011 [2] => 04.06.2011 [3] => 04.07.2011 [4] => 04.08.2011 [5] => 04.09.2011 )

For each of the dates in the array, I want to run a query that returns the average a column:

foreach ($dateMonthYearArr as $csm_sum_for_date){
//Run query and calc occ average for each day
echo "Date - $csm_sum_for_date<br />";
$drillq = mysql_query("$csm_query statistic_date='$csm_sum_for_date' LIMIT 700" );// or die(mysql_error());
while($row_drillq = mysql_fetch_array( $drillq )) {
$hnl_tme = $row_drillq['tlk_tme'] + $row_drillq['hld_tme'] + $row_drillq['acw_tme']; //correct!
$avl_tme_occ = $row_drillq['sum_avl_tme']/$row_drillq['cnt_ph_skll']; //correct!
$occ = $hnl_tme+$avl_tme_occ;
$csm_occ = $hnl_tme/$occ*100;//For csm averaging
$occ = $hnl_tme/$occ*100; //correct!
$occ = round("$occ", 0);
$stat_date = $row_drillq['statistic_date'];
$csm_occ_array[] = "$csm_occ";//For csm averaging
echo "$i - $csm_occ | $row_drillq[agent]<br />";
print_r ($csm_occ_array);
$csm_daily_occ_sum = array_sum($csm_occ_array);
$csm_daily_occ_sum_percent = $csm_daily_occ_sum/$i;
$csm_daily_occ_sum_percent = round("$csm_daily_occ_sum_percent", 0);
echo "<br /><b>$i - $csm_daily_occ_sum - $csm_daily_occ_sum_percent% </b><br /><br />";

The problem I have is that I'm not sure how to get the percentage and sum for EACH day individually. What I have compounds the averages and sum as it progresses (for example: if day 1 = 10 and day 2 = 5, it shows the day 1 sum as 10 and the day two sum as 15).

This is obviously because my array_sum is outside the while but if I were to put it inside the while, it wouldn't help because it's only dealing with the one record.

I'm scratching my head on this one -- not something I've ever had a reason to work with before. Any ideas?

Thanks in advance!

05-27-2011, 02:49 PM
Does this make sense to anyone or do I need to provide clarifying information?

05-27-2011, 03:38 PM
Mysql has a built-in function to find the average (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg) of a set of values. Have you considered that?

What'd you get if you echo the string
"$csm_query statistic_date='$csm_sum_for_date' LIMIT 700" ?

05-29-2011, 03:33 PM
Simply summing in the query wouldn't work because I need to take the individual values and calcuate some other stuff. After some more thought and a few hours away from the problem, I came up with this solution (which works perfectly)...

$j=0;//Set j=0 -- This will prevent the array_sum from adding past days.
foreach ($dateMonthYearArr as $csm_sum_for_date){
for ($m = 0; $m <= $j; $m++) {
$csm_occ_array[$m] = 0;

//Query and build array
$i++; in mysql while statement

for ($m = 0; $m <= $j; $m++) {
$csm_occ_array[$m] = 0;

Thanks for your help!