CODEWITHSUNDEEP
mongodbaggregation-frameworkweighted-average
derdo
derdo

Reputation: 1036

How do I calculate a weighted average in mongoDB using aggregation framework?

I need to calculate weighted average over a set of documents. Each document contains both a weight and a value in a separate field. Here is an example: I have following 2 documents representing a sales transaction.

{
    quantity : 3,
    price : 2
}

{
    quantity : 9,
    price : 6
}

I want to find the average price for both transactions. This is a weighted average where weight is the quantity and value is the price. This can be calculated by

AveragePrice = (3 * 2 + 9 * 6 ) / (3 + 9).

How do I perform this calculation using aggregation framework?

Upvotes: 3

Views: 4502

Answers (1)

Leonid Beschastny
Leonid Beschastny

Reputation: 51480

To do so you should first calculate numerator (weighted sum) and denominator (sum of weights) of the resulting ratio. After that you'll only need to divide one by another:

db.collection.aggregate({
  $group : {
     _id : 'weighted average', // build any group key ypo need
     numerator: { $sum: { $multiply: [ "$price", "$quantity" ] } },
     denominator: { $sum: "$quantity" }
  }
}, {
  $project: {
    average: { $divide: [ "$numerator", "$denominator" ] }
  }
})

For more info see Aggregation Pipeline documentation.

Upvotes: 17

Related Questions