I am trying to count distinct(not unique) or Emp No in same department.but getting error

query failed: unknown group operator '$group'

here is my code

db.collection.aggregate([
  {
    $group: {
      _id: "$Department",
      total: {
        "$group": {
          _id: "$Emp No"
        }
      }
    }
  }
])

Expected output

[
  {
    "_id": "HUAWEI”,
“total”:1
  },
  {
    "_id": "THBS”,
“total”:2
  }
]

THBShave two different Emp No A10088P2C and A20088P2C

HUAWEI have only one Emp No A1016OBW

I am trying to count distinct(not unique) or Emp No in same department.but getting error

query failed: unknown group operator '$group'

here is my code https://mongoplayground/p/UvYF9NB7vZx

db.collection.aggregate([
  {
    $group: {
      _id: "$Department",
      total: {
        "$group": {
          _id: "$Emp No"
        }
      }
    }
  }
])

Expected output

[
  {
    "_id": "HUAWEI”,
“total”:1
  },
  {
    "_id": "THBS”,
“total”:2
  }
]

THBShave two different Emp No A10088P2C and A20088P2C

HUAWEI have only one Emp No A1016OBW

• javascript
• node.js
• mongodb
• mongoose
• mongodb-query

• It's not quite clear what you are trying to count. There is no $group accumulator. Available accumulators are documented on docs.mongodb./manual/reference/operator/aggregation/group/…. – Alex Blex Commented Sep 16, 2019 at 8:50

1 Answer 1

so, $group is Pipeline stage, you can only use it in upper level.

But for your required output there is lots of ways i believe, we can do something like this as well:

db.collection.aggregate([
  {
    $group: {
      _id: {
        dept: "$Department",
        emp: "$Emp No"
      },
      total: {
        "$sum": 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.dept",
      total: {
        "$sum": 1
      }
    }
  }
])

Here, in first stage we are grouping with Department and its Emp No , and also we are having count of how many Emp No is in each dept. [this count you can remove though as we are not using it.]

result of this stage will be:

[
  {
    "_id": {
      "dept": "THBS",
      "emp": "A10088P2C"
    },
    "total": 2
  },
  {
    "_id": {
      "dept": "THBS",
      "emp": "A20088P2C"
    },
    "total": 1
  },
  {
    "_id": {
      "dept": "HUAWEI",
      "emp": "A1016OBW"
    },
    "total": 3
  }
]

next on top of this part data, i'm grouping again, with the dept. which es in $_id.dept, and making count in the same way, which gives the result in your required format.

[
  {
    "_id": "HUAWEI",
    "total": 1
  },
  {
    "_id": "THBS",
    "total": 2
  }
]

Demo