MongoDB SELECT COUNT GROUP BY

1. Overview

In this article, we will learn to implement the SQL query SELECT COUNT GROUP BY in the MongoDB database.

You can use aggregation operation that process multiple documents and return computed results:

• Group values from multiple documents together.
• Perform operations on the grouped data to return a single result.

2. MongoDB SELECT COUNT GROUP BY

An aggregation pipeline comprises one or more stages that process documents.

MongoDB aggregation framework provides the $group stage to group documents based on a field or group of fields.

The $group stage separates documents into groups according to the specified “group key”. The output is one document for each unique group key.

A group key is often a field, or group of fields. The group key can also result from an expression.

You must use the _id field in the $group pipeline stage to set the group key. The output documents can also contain additional fields that are set using accumulator expressions. Here, you must use $sum accumulator expression to group and count the documents of each group.

The syntax of the $group stage:

{
  $group:
    {
      _id: <expression>, 
      <field1>: { <accumulator1> : <expression1> },
      ...
    }
 }

2.1. MongoDB group by a single field and count

Assume you have a collection of Student grades.

_id: ObjectId('56d5f7eb604eb380b0d8d8ce')
student_id: 1
scores: Array
class_id: 339

If you use the below aggregator query, then you can count the number of documents in each group.

[
  {
    '$group': {
      '_id': '$class_id', 
      'count': {
        '$sum': 1
      }
    }
  }
]

    $group: { 
        _id: "$class_id", 
        count: { $sum: 1 }
    } 
} ] )

Result would be something like below:

{
_id: 124
count:185
},
{
_id: 406
count:201
}

2.2. MongoDB group by multiple fields

Consider you have a list of zip documents:

_id: ObjectId('5c8eccc1caa187d17ca6ed16')
city:"ALPINE"
zip:"35014"
loc: Object
   y:33.331165
   x:86.208934
pop:3062
state:"AL"

You can group by multiple fields as group key such as state and zip and count the number of documents in each group.

For example, the following aggregator query takes both zip and state as a group key and applies the accumulator function $sum on it.

{
  _id: {zip:'$zip', state:'$state'},
  count: {
    $sum: 1
  }
}

If you execute the above query, it produces the following result:

{
_id: Object
zip: "81044"
state: "CO"
count:1
},
{
_id: Object
zip:"13327"
state:"NY"
count:1
}

3. Conclusion

To sum up, we have learned to use MongoDb query to group documents and count. You can learn more about MongoDB from these articles.