Consider I have the following collection:

[
  {
    "total": 48.0,
    "status": "CO"
  },
  {
    "total": 11.0,
    "status": "CA"
  },
  {
    "total": 15916.0,
    "status": "PE"
  }
]

I need to realize the difference of PE status - (CO CA).

The expected result is:

{
  "_id" : null,
  "total" : 15857.0
}

CodePudding user response：

Use $switch to cater for different cases for your sum. Use $subtract to flip the sign for the partial sum.

db.collection.aggregate([
  {
    $group: {
      _id: null,
      total: {
        "$sum": {
          "$switch": {
            "branches": [
              {
                "case": {
                  $eq: [
                    "$status",
                    "PE"
                  ]
                },
                "then": "$total"
              },
              {
                "case": {
                  $eq: [
                    "$status",
                    "CO"
                  ]
                },
                "then": {
                  $subtract: [
                    0,
                    "$total"
                  ]
                }
              },
              {
                "case": {
                  $eq: [
                    "$status",
                    "CA"
                  ]
                },
                "then": {
                  $subtract: [
                    0,
                    "$total"
                  ]
                }
              }
            ],
            default: 0
          }
        }
      }
    }
  }
])

Mongo Playground

CodePudding user response：

Assuming these are the only status options, one way is to $group using $cond:

db.collection.aggregate([
  {$group: {
      _id: 0,
      total: {
        $sum: {$cond: [{$eq: ["$status", "PE"]}, "$total", {$multiply: ["$total", -1]}]}
      }
  }}
])

See how it works on the playground example