CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
megamind
megamind

Reputation: 444

Calculating percentage within a group in mongodb aggregate pipeline

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
        }

]

Upvotes: 2

Views: 706

Answers (1)

Valijon
Valijon

Reputation: 13103

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

Upvotes: 2

Related Questions