Home > database >  mongodb aggregate group sum
mongodb aggregate group sum

Time:11-04

I have some mongodb problems and need help.

This is the creation statement of the document:

db.getCollection("test").insert( {
_id: ObjectId("5b0cf67270e4fa02d31de42e"),
name: "xiaoming",
time: 400,
mon: NumberDecimal("100.7645234")
} );

db.getCollection("test").insert( {
_id: ObjectId("5b0cf6ad70e4fa02d31de430"),
name: "xiaoming",
time: 0,
mon: NumberDecimal("1.23734324")
} );

db.getCollection("test").insert( {
_id: ObjectId("5b0d14c870e4fa02d31de436"),
name: "xiaoming",
time: 0,
mon: NumberDecimal("1.785233")
});

I hope to get such a result:

name total_count
xiaoming 103.79

In other words, the mon field in the document needs to be rounded first and then rounded. Each mon field is formatted as a number with two decimal places, and then the final summation operation is carried out.Instead of simply rounding the final sum.

This is the statement I have written so far, but obviously the result is not what I want:

db.test.aggregate([
{
    '$group': {
        _id: '$name'
    }
},
{
    '$project': {
        total_count: {
            '$sum': {
                '$divide': [{
                    '$trunc': {
                        '$add': [{
                            '$multiply': ['$mon', 100]
                        }, 0.5]
                    }
                }, 100]
            }
        }
    }
 }
])

The code I write can only achieve this effect:

name total_count
xiaoming 0

Obviously, the code I wrote was wrong.

help me, thanks you!

CodePudding user response:

Your aggregation is incorrect, the $project stage is performed after the $group stage and therefore only has access to the output document from the $group stage which does not contain the $mon field. Aggregates can be calculated within the same $group stage. Also, note that MongoDB provides a $round expression that simplifies the aggregation expression:

db.collection.aggregate([
  {
    "$group": {
      _id: "$name",
      total_count: {
        "$sum": {
          "$round": [
            "$mon",
            2
          ]
        }
      }
    }
  }
])

try it in mongo playground

Your original expression works as well, if executed within the $group instead of the $project stage:

db.collection.aggregate([
  {
    "$group": {
      _id: "$name",
      total_count: {
        "$sum": {
          "$divide": [
            {
              "$trunc": {
                "$add": [
                  {
                    "$multiply": [
                      "$mon",
                      100
                    ]
                  },
                  0.5
                ]
              }
            },
            100
          ]
        }
      }
    }
  }
])

CodePudding user response:

Query

  • try this its your code, just inside an accumulator

Test code here

aggregate(
[{"$group": 
    {"_id": "$name",
      "count": 
      {"$sum": 
        {"$divide": 
          [{"$trunc": {"$add": [{"$multiply": ["$mon", 100]}, 0.5]}}, 100]}}}},
  {"$set": {"name": "$_id"}},
  {"$project": {"_id": 0}}])
  • Related