CODEWITHSUNDEEP
mongodbaggregation-framework
PushkarRode
PushkarRode

Reputation: 55

How to calculate percentage using MongoDB aggregation

I want to calculate percentage of with help of mongoDB aggregation, My collection has following data.

subject_id gender other_data
1 Male XYZ
1 Male ABC
1 Male LMN
2 Female TBZ
3 Female NDA
4 Unknown UJY

I want output something like this:

[{
  gender: 'Male',
  total: 1,
  percentage: 25.0
},{
  gender: 'Female',
  total: 2,
  percentage: 50.0
},{
  gender: 'Unknown',
  total: 1,
  percentage: 25.0
}]

I have tried various methods but none of them works, mainly unable to count total of Male, Female, Unknown summation(to calculate percentage). The trickiest part is there are only 4 members in above example but their subject_id may be repeated according to other_data

Thanks in Advance.

Upvotes: 2

Views: 3099

Answers (1)

J.F.
J.F.

Reputation: 15207

You can use this aggregation query:

  • First group by subject_id to get the different values (different persons).
  • Then use $facet to create "two ways". One to use $count and get the total number of docs, and other to get the documents grouped by gender.
  • Then with all desired values (grouped by gender and total docs) get the first element of the result from nDocs into $facet stage. $facet will generate an array and the value we want will be in the first position.
  • Later use $unwind to get every groupValue with the nDoc value
  • And last output the values you want using $project. To get the percentage you can $divide total/nDocs and $multiply by 100.
db.collection.aggregate([
  {
    "$group": {
      "_id": "$subject_id",
      "gender": {
        "$first": "$gender"
      }
    }
  },
  {
    "$facet": {
      "nDocs": [
        {
          "$count": "nDocs"
        },
        
      ],
      "groupValues": [
        {
          "$group": {
            "_id": "$gender",
            "total": {
              "$sum": 1
            }
          }
        },
        
      ]
    }
  },
  {
    "$addFields": {
      "nDocs": {
        "$arrayElemAt": [
          "$nDocs",
          0
        ]
      }
    }
  },
  {
    "$unwind": "$groupValues"
  },
  {
    "$project": {
      "_id": 0,
      "gender": "$groupValues._id",
      "total": "$groupValues.total",
      "percentage": {
        "$multiply": [
          {
            "$divide": [
              "$groupValues.total",
              "$nDocs.nDocs"
            ]
          },
          100
        ]
      }
    }
  }
])

Example here

Upvotes: 3

Related Questions