CODEWITHSUNDEEP
mongodbmongodb-query
user13380066
user13380066

Reputation:

MongoDB query to group and count records greater than and smaller than a certain value

Could you please help in correcting this query to group and count Continente based on PIB value greater than and smaller than 1.3?

db.collection.aggregate([
  {
    $project:{
      "item":1,
      "PIB":1
    }
  },
  {
    $addFields:{
      moreThan1.3:{
        $cond:[
          {
            $gt:[
              "$PIB",
              1.3
            ]
          },
          1,
          0
        ]
      },
      lessThan1.3:{
        $cond:[
          {
            $lte:[
              "$PIB",
              1.3
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    $group:{
      "_id":"$Continente",
      "countSmaller":{
        $sum:"$lessThan1.3"
      },
      "countBigger":{
        $sum:"$moreThan1.3"
      }
    }
  }
]).pretty()

This is the database in Robo 3T/MongoDB : Database in MongoDB

This error is appearing:

Error: Line 10: Unexpected number

Thank you!

Upvotes: 0

Views: 1844

Answers (2)

Vijay Rajpurohit
Vijay Rajpurohit

Reputation: 1352

I have updated the query, you were close, I used $addFields to store the field.

db.collection.aggregate([
  {
    $project:{
      "item":1,
      "PIB":1
    }
  },
  {
    $addFields:{
      moreThan10:{
        $cond:[
          {
            $gt:[
              "$PIB",
              10
            ]
          },
          1,
          0
        ]
      },
      lessThan10:{
        $cond:[
          {
            $lte:[
              "$PIB",
              10
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    $group:{
      "_id":"$Continente",
      "countSmaller":{
        $sum:"$lessThan10"
      },
      "countBigger":{
        $sum:"$moreThan10"
      }
    }
  }
]).pretty()

Hope this will help :)

Update

db.collection.aggregate([
  {
    $project:{
      "item":1,
      "PIB":1
    }
  },
  {
    $addFields:{
      "moreThan1.3":{
        $cond:[
          {
            $gt:[
              "$PIB",
              1.3
            ]
          },
          1,
          0
        ]
      },
      "lessThan1.3":{
        $cond:[
          {
            $lte:[
              "$PIB",
              1.3
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    $group:{
      "_id":"$Continente",
      "countSmaller":{
        $sum:"$lessThan1.3"
      },
      "countBigger":{
        $sum:"$moreThan1.3"
      }
    }
  }
]).pretty()

Upvotes: 1

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Try this aggregation query :

db.collection.aggregate([
  {
    $group: {
      _id: "$continente",
      /** counting docs based on condition, for an on-going doc if condition is met pass in 1 or 0 */
      countSmaller: { $sum: { $cond: [{ $lte: ["$PIB", 1.3] }, 1, 0] } },
      countBigger: { $sum: { $cond: [{ $gt: ["$PIB", 1.3] }, 1, 0] } }
    }
  }
]);

Test : mongoplayground

Upvotes: 2

Related Questions