Weighted means in MySQL and/or PHP -

i have database:

id     apple    orange    p      q 1      3        2         0      1 2      4        1         1      1 3      2        2         0      0 

i want calculate means of fruit columns, each value weighted sum of p + q in row.

mean_of_apple = ( 3 + 4 + 2) / 3 = 3  weighted_mean_of_apple = = ( (3 * (0 + 1)) + (4 * (1 + 1)) + (2 * (0 + 0)) ) / ( (0 + 1) + (1 + 1) + (0 + 0) ) = 3.6667 

normally, arithmetic means of each column, like:

select avg(apple), avg(orange) my_table 

how can weighted means without complicated loops , storing of intermediary results?

i query database php, calculation of means can done in either mysql, php or combination of both.

i have this:

$query = "select * fruits"; // ... // php queries database , gets results // ... $apple = array(); $orange = array(); while($row = $result->fetch_assoc()) {     for($i = 0; $i < ($row["ch1"] + $row["ch2"]); $i++) {         $apple[] = $row["apple"];         $orange[] = $row["orange"];     } } $mean_apple = array_sum($apple) / count($apple); $mean_orange = array_sum($orange) / count($orange); 

but wonder, if there better way – , if correct.

please note real table has 5 fruits , 4 weights, if makes difference answer.

you can following query:

select avg(apple) appleavg,         sum(apple * (p+ q)) / sum(p + q) wappleavg,        avg(orange) orangeavg,        sum(orange * (p + q)) / sum(p + q) worangeavg mytable 

demo here