CODEWITHSUNDEEP
phpmysql
user1322720
user1322720

Reputation:

Weighted means in MySQL and/or PHP

I have a database:

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

I want to calculate the means of the fruit columns, with each value weighted by the sum of p + q in that 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, to get the arithmetic means of each column, I would do something like:

SELECT AVG(apple), AVG(orange) FROM my_table

How can I get the weighted means without some complicated loops and storing of intermediary results?

I query the database with PHP, so the calculation of the means can be done in either MySQL, PHP or a combination of both.

I have this:

$query = "SELECT * FROM fruits";
// ...
// PHP queries the database and gets the 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 I wonder, if there is a better way – and if this is even correct.

Please note that the real table has five fruits and four weights, if that makes a difference for your answer.

Upvotes: 1

Views: 91

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can do it with the following query:

SELECT AVG(apple) AS appleAvg, 
       SUM(apple * (p+ q)) / SUM(p + q) AS wAppleAvg,
       AVG(orange) AS orangeAvg,
       SUM(orange * (p + q)) / SUM(p + q) AS wOrangeAvg
FROM mytable

Demo here

Upvotes: 2

Related Questions