MySQL/PHP SUM data in an ARRAY

Question

I have the following query:

SELECT AVG (q1) AS q1, AVG (q2) AS q2, AVG (q3) AS q3 
FROM thotels_results 
WHERE brand IN ('ABC','EFG','XYZ') 
AND date = 'NOV2010' GROUP BY brand;

I LOOP through the results and have dumped out the array using var_dump, the output is below:

array
  'q1' => string '8.1724' (length=6)
  'q2' => string '8.2414' (length=6)
  'q3' => string '8.2414' (length=6)

array
  'q1' => string '8.7714' (length=6)
  'q2' => string '8.8429' (length=6)
  'q3' => string '8.1643' (length=6)

array
  'q1' => string '8.6009' (length=6)
  'q2' => string '8.5686' (length=6)
  'q3' => string '7.8528' (length=6)

What I would like to do is, somehow, add each of the q1's together and divide by 3 to give me an average, then the same for q2 and the same for q3:

8.1724 + 8.7714 + 8.6009 / 3 = 8.5149

I'm not sure how to access the information stored in the array - thanks in advance.

Homer.

Mark Baker · Accepted Answer · 2010-12-07 09:09:40Z

2

$q1Ave = $q2Ave = $q3Ave = 0
foreach ($results as $qValues) {
   $q1Ave += $qValues['q1'];
   $q2Ave += $qValues['q2'];
   $q3Ave += $qValues['q3'];
}
$q1Ave /= count($results);
$q2Ave /= count($results);
$q3Ave /= count($results);

answered Dec 7, 2010 at 9:09

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cristian · Accepted Answer · 2010-12-07 09:59:26Z

2

You can try
SELECT SUM(q1)/3 as q1, SUM(q2)/3 as q2, SUM(q3)/3 as q3 FROM ( SELECT AVG (q1) AS q1, AVG (q2) AS q2, AVG (q3) AS q3 FROM thotels_results WHERE brand IN ('ABC','EFG','XYZ') AND date = 'NOV2010' GROUP BY brand ) as derived_table;

edited Dec 7, 2010 at 9:59

answered Dec 7, 2010 at 9:16

cristian

8,7443 gold badges40 silver badges44 bronze badges

3 Comments

Homer_J Over a year ago

Hi, tried that but getting the following error: #1248 - Every derived table must have its own alias

cristian Over a year ago

Sorry for that. I just added an alis for derived table called as derived_table

Homer_J Over a year ago

Perfect! Very very elegant answer!

Shakti Singh · Accepted Answer · 2010-12-07 09:15:08Z

1

before loop start

$q1sum=0;
$q2sum=0;
$q3sum=0;
$count=0

put this inside the loop you traversed.

$q1sum+=(float) $array['q1'] ;
$q2sum+=(float) $array['q2'] ;
$q3sum+=(float) $array['q3'] ;
$count++;

put this out of the loop after the loop end.

$q1final_result=$q1sum/$count;
$q2final_result=$q2sum/$count;
$q3final_result=$q3sum/$count;

edited Dec 7, 2010 at 9:15

answered Dec 7, 2010 at 9:08

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Comments

user319198 · Accepted Answer · 2010-12-07 09:14:08Z

1

$tab = array(
                 0 => array ("q1" => 2,"val2" => 5),
                 1 => array ("q1" => 6,"val2" => 10),
                 2 => array ("q1" => 15,"val2" => 50),
                );

echo sum_subarrays_by_key( $tab, "q1" );



function sum_subarrays_by_key( $tab, $key ) {

        $sum = 0;

        foreach($tab as $sub_array) {
            $sum += $sub_array[$key];
        }

        return $sum;

    }

It will work at every depth of array. need to call by changing key which u want to sum in calling function

answered Dec 7, 2010 at 9:14

user319198

Collectives™ on Stack Overflow

MySQL/PHP SUM data in an ARRAY

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related