Average of average MongoDB Charts

MongoDB Atlas MongoDB Charts
1

Hi I have a problem with the way mongo charts calculates the average of documents. After an aggregation I calculate the percentage of a specific field. Which gives the correct value for each individual document. But when it is averaged over all the documents is uses the average of each document to calculate an average. This yields the wrong result as it takes the average of averages. I want the average to be calculated by still using the average of the count of the 2 fields to give an overall average.

2

Hello, @johan_potgieter!

Let’s create an example dataset to work with:

db.players.insertMany([
 { playerName: 'Bob', playerAge: 27, belongsToTeam: 'A' },
 { playerName: 'Jack', playerAge: 33, belongsToTeam: 'A' },
 { playerName: 'Bill', playerAge: 21, belongsToTeam: 'B' },
 { playerName: 'Sam', playerAge: 22, belongsToTeam: 'B' },
 { playerName: 'Sam', playerAge: 23, belongsToTeam: 'B' },
]);

Let’s assume, we want to have the following averages in the output:

{
  "teams": [
    {
      "team": "B",
      // (21+22+23)/3 = 22
      "averageAgeInTeam": 22
    },
    {
      "team": "A",
      // (27+33)/2 = 30
      "averageAgeInTeam": 30
    }
  ],
  // (21+22+23+27+33)/5 = 25.2
  "averageAgeInAllTeams": 25.2
}

This can be achieved with this aggregation:

db.players.aggregate([
 {
   $group: {
     _id: '$belongsToTeam',
     averageAgeInTeam: {
       $avg: '$playerAge',
     }
   }
 },
 {
   $group: {
     _id: null,
     teams: {
       $push: {
         team: '$_id',
         averageAgeInTeam: '$averageAgeInTeam',
       }
     },
     averageAgeInAllTeams: {
       $avg: '$averageAgeInTeam',
     }
   }
 }
]).pretty();

db.players.aggregate([
 {
   $group: {
     _id: '$belongsToTeam',
     agesOfPlayers: {
       $push: '$playerAge',
     },
     averageAgeInTeam: {
       $avg: '$playerAge',
     }
   }
 },
 {
   $group: {
     _id: null,
     teams: {
       $push: {
         team: '$_id',
         averageAgeInTeam: '$averageAgeInTeam',
       }
     },
     arraysOfAgesOfPlayers: {
       $push: '$agesOfPlayers',
     }
   }
 },
 {
   $addFields: {
     singleArrayOfAgesOfPlayers: {
       $reduce: {
         input: '$arraysOfAgesOfPlayers',
         initialValue: [],
         in: {
           $concatArrays: ['$$value', '$$this'],
         }
       }
     }
   }
 },
 {
   $project: {
     teams: true,
     averageAgeInAllTeams: {
       $avg: '$singleArrayOfAgesOfPlayers',
     }
   }
 }
]).pretty();