1

Calculate percentage within a group in mongodb using aggregation pipeline. Suppose I have a collection like shown below with some documents.

 [{
 "Name":"xyz" ,
 "Area":"London",
 "Sex":"Male"
 },
 {
"Name":"aaa" ,
 "Area":"London",
 "Sex":"Female"
},

{
"Name":"bbb" ,
 "Area":"London",
 "Sex":"Female"
},
{
"Name":"ccc" ,
 "Area":"London",
 "Sex":"Female"
},
 {
"Name":"abc" ,
 "Area":"Chile",
 "Sex":"Female"
},
 {
"Name":"xxx" ,
 "Area":"Chile",
 "Sex":"Male"
}

]

I want percentage of male and female by each area. Expected output should be something like.

 [
        {
            _id {
                area : 'London', Sex:'Male'
            },

            percentage: 25
        },
       {
            _id {
                area : 'London', Sex:'Female'
            },

            percentage: 75
        },

        {
            _id {
                area : 'Chile', Sex:'Female'
            },

            percentage: 50
        },
         {
            _id {
                area : 'Chile', Sex:'Male'
            },

            percentage: 50
        }

]
Valijon
  • 12,667
  • 4
  • 34
  • 67
megamind
  • 424
  • 1
  • 4
  • 19
  • Show some more documents and the expected output with explanation. – Ashh Apr 11 '20 at 13:36
  • @Ashh Ok I am updating the ques but I want exact same answer as this question but its in sql..Will this work https://stackoverflow.com/questions/1823599/calculating-percentage-within-a-group – megamind Apr 11 '20 at 13:43
  • @Ashh Updated the ques with expected ans – megamind Apr 11 '20 at 14:17

1 Answers1

1

We need to group by Area + Sex to calculate total value. Then, we group again only by area and calculate percentage.

Try this one:

db.collection.aggregate([
  {
    $group: {
      _id: {
        area: "$Area",
        Sex: "$Sex"
      },
      total: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.area",
      area: {
        $push: "$$ROOT"
      },
      total: {
        $sum: "$total"
      }
    }
  },
  {
    $addFields: {
      area: {
        $map: {
          input: "$area",
          in: {
            _id: "$$this._id",
            percentage: {
              $multiply: [
                {
                  $divide: [ "$$this.total", "$total" ]
                },
                100
              ]
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$area"
  },
  {
    $replaceRoot: {
      newRoot: "$area"
    }
  }
])

MongoPlayground

Valijon
  • 12,667
  • 4
  • 34
  • 67