Question 117098 in Mongodb Question posted in
The official documentation can be found here.

how to calculate percentage of individual field in MongoDB using aggregation query

I want to calculate the percentage of the position of a football player field-wise to the country they belong to.
Let’s say, there are a total 800 players from Nepal whereas only 20 were goal keepers so percentage would be Nepal: 2.5 %

My Dataset

[{
"position":"defenser",
"country":"Nepal"
},
{
"position":"defenser",
"country":"Nepal"
},
{
"position":"goal keeper",
"country":"Nepal"
},
{
"position":"goal keeper",
"country":"France"
},
{
"position":"defenser",
"country":"France"
},
{
"position":"goal keeper",
"country":"France"
},
{
"position":"defenser",
"country":"France"
},
{
"position":"defenser",
"country":"Nepal"
}
]

there are 3 defenser from Nepal out of total 4 and 2 defenser from france out of 4.

Expected output
:

{"country":"Nepal", "position": "defenser", "percentage":"75"}
{"country":"France", "position": "defenser", "percentage":"50"}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As far as this data set and the problem is concerned we can get the appropriate result without using $$ROOT and $replaceRoot functions too.

    Solution:

    1. Group country-wise and get the total and push the position
    2. Then unwind the array of position

    3 Then multiple groups of country and positions and there individual sum.

    4 In last project stage calculate the percentage

    db.collection.aggregate([{
 $group: {
  _id: '$country',
  sum: {
   $sum: 1
  },
  position: {
   $push: '$position'
  }
 }
}, {
 $project: {
  _id: 0,
  country: '$_id',
  sum: 1,
  position: 1
 }
}, {
 $unwind: {
  path: '$position'
 }
}, {
 $group: {
  _id: {
   position: '$position',
   country: '$country'
  },
  individualSum: {
   $sum: 1
  },
  sum: {
   $first: '$sum'
  }
 }
}, {
 $project: {
  _id: 0,
  position: '$_id.position',
  country: '$_id.country',
  individualSum: 1,
  sum: 1,
  percentage: {
   $multiply: [
    {
     $divide: [
      '$individualSum',
      '$sum'
     ]
    },
    100
   ]
  }
 }
}])

  2. 0

    Query

    • group by country and position, and count
    • group by country, to calculate the totalSum for that country
    • map dividing the count/totalSum and * 100
    • unwind and replace root
    • result is, for each country the percentage each position takes

    Playmongo

    aggregate(
[{"$group": 
   {"_id": {"country": "$country", "position": "$position"},
    "sum": {"$sum": 1}}},
 {"$group": 
   {"_id": "$_id.country",
    "players": {"$push": "$$ROOT"},
    "totalsum": {"$sum": "$sum"}}},
 {"$set": 
   {"players": 
     {"$map": 
       {"input": "$players",
        "in": 
         {"position": "$$this._id.position",
          "country": "$_id",
          "percentage": 
           {"$multiply": 
             [{"$divide": ["$$this.sum", "$totalsum"]}, 100]}}}}}},
 {"$unwind": "$players"}, {"$replaceRoot": {"newRoot": "$players"}}])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search