CODEWITHSUNDEEP
javascriptmongodbmongoosemongodb-queryaggregation-framework
Matheus Barem
Matheus Barem

Reputation: 1627

Mongoose: $sum if conditions

I have a query with find and aggregate that find in Schedule model, and calculate the sum of total of services and the sum of total value of services. But I need to make this sum (sum of totalServices) with a condition, where the ´status´ equal to 2. can i make this?

My query:

 Schedule.find(findTerm)
    .skip(req.body.page * req.body.limit)
    .limit(Number(req.body.limit))
    .select(
      "service.name value scheduleStart scheduleEnd comissionValue status paymentMethod"
    )
    .exec((err, response) => {
      if (err) res.status(500).send(err);

      Schedule.find(findTerm)
        .count()
        .exec((error, count) => {
          if (error)
            res.status(500).send({
              error,
              code: 0,
              message: "Erro."
            });

          Schedule.aggregate([{
              $match: {              
                store: req.body.store,             
              }
            },
            {
              $group: {
                _id: {
                  id: "$store"
                },
                totalValue: {
                  $sum: "$value"
                },
                totalServices: {
                  $sum:  {
                    $cond: [ {
                      $eq: [ "$status", 2 ]
                    }]
                  }
                },
                count: {
                  $sum: 1
                }
              }
            }...

Result of my query:

...{
            "service": {
                "name": "CABELO + BARBA"
            },
            "comissionValue": 0,
            "paymentMethod": 0,
            "_id": "5bfec336c6f00d2e88f8d765",
            "scheduleStart": "2018-11-28 14:35",
            "scheduleEnd": "2018-11-28 15:45",
            "status": 2,
            "value": 75
        },
        {
            "service": {
                "name": "Barba"
            },
            "comissionValue": 0,
            "paymentMethod": 0,
            "_id": "5bfec3ffc6f00d2e88f8d766",
            "scheduleStart": "2018-11-28 18:30",
            "scheduleEnd": "2018-11-28 18:50",
            "status": 2,
            "value": 20
        }
    ],
    "count": 4299,
    "group": [
        {
            "_id": {
                "id": "5b16cceb56a44e2f6cd0324b"
            },
            "totalValue": 777780048281, //right value
            "totalServices": 945, //wrong value
            "count": 676
        }
    ]
}

I need to filter the sum of totalServices to only objects if the status equal to 2 (I tried to use $cond but not worked).

Upvotes: 1

Views: 3445

Answers (1)

Ashh
Ashh

Reputation: 46441

You need to check the status conditionally($cond) i.e. if status is equal ($eq) to 2 then $sum the value field else pass 0

Schedule.aggregate([
  { "$match": { "store": req.body.store }},
  { "$group": {
    "_id": { "id": "$store" },
    "totalValue": { "$sum": "$value" },
    "totalServices": {
      "$sum": { "$cond": [{ "$eq": ["$status", 2] }, 1, 0] }
    },
    "count": { "$sum": 1 }
  }}
])

Upvotes: 1

Related Questions