MongoDB Equivalent of SELECT COUNT GROUP BY

The SELECT COUNT GROUP BY procedure in MongoDB combines the GROUP BY and COUNT methods. The count method is employed to determine the occurrence of documents in a collection that satisfy specific conditions. Next, we can count the documents based on certain grouping criteria using the group() method.

1. Get Group by Count in MongoDB equivalent to SQL SELECT COUNT GROUP BY

Group By on certain fields and getting the count of values for each group is the most common requirement in MongoDB or any database, in SQL queries this is usually referred to SELECT COUNT GROUP BY. In MongoDB, you can perform the group by using the $group stage and get the count using the $sum operator. Let’s see an example


# Group documents by a single field 
db.student.aggregate([
  { $group: {
    _id: "$name",
    count: { $sum: 1 }
  }}
]);

Here, we have a query where the $group stage groups documents by the name field, and the $sum operator is used to count the occurrence of documents in each group.

The above example yields the below output, where each document contains the _id field as the name of the student and the count field as the occurrence of the student’s name.

2. Group by Multiple fields and Count of each group in MongoDB

Additionally, you can also group documents by multiple fields by providing an array of field names to the $group stage. Here, in the following query the pipeline groups the documents by the age and name fields and counts the occurrence of documents in each group using the $sum operator.

This query will return the documents, where each document contains the _id field with the values of the grouped fields age and name and the count field with the number of documents in the group.


# Group documents by multiple fields
db.student.aggregate([
  { $group: {
    _id: { Age: "$age", Name: "$name" },
    count: { $sum: 1 }
  }}
]);

The output yielded below counts the document by the group fields and groups the field into the _id field.

3. Filtered by a Condition & Perform Group By COUNT

You can also count the occurrence of the documents in the collection by filtering the result set using the $match stage before the $group stage.

#Usage of $match stage
db.student.aggregate([
  { $match: { "name": "Ian" } },
  { $group: {
    _id: "$course",
    count: { $sum: 1 }
  }}
]);

In this query, we used the $match operator to filter documents where the name field is equal to Ian. Then, we group the documents by the course field and count the number of documents in each group using the $sum operator.

The above example yields the below result where each document contains the _id field as the course field value and the count field has the occurrence of the filtered document.

4. Count the documents grouped by a field and sorted by count

Moreover, we can count the number of documents in a collection grouped by a field and sort them by the count in a specific order.

In the following query, we used the $group operator to group the documents by the specified field course. We have used the $sum operator to count the number of documents in each group, storing the count in a count field. After that, depending on the field count, the $sort operator arranges the outcomes in descending order.

#Counting documents number in a collection
db.student.aggregate([
  { $group: {
    _id: "$course",
    count: { $sum: 1 }
  }},
  { $sort: { count: -1 } }
]);

The output showed each document contains the _id field which has a value of the grouped field course. Also, the count field contains the count of documents in the group, sorted by the count in descending order.

5. Count Distinct Values

Now, there might be a case when we want to count the number of distinct values in the orders collection. We must employ the subsequent query to accomplish this.

Here, we first group the documents by the name field to get the distinct student names. Then, it groups the distinct student names by a null value to count the total number of distinct names. After that, the _id field as null and the count field as the number of distinct names will be returned.

#Counting distinct values
db.student.aggregate([
  { $group: { _id: "$name" } },
  { $group: { _id: null, count: { $sum: 1 } } }
])

The output is obtained for counting the occurrence of distinct values by the group field.

6. Conclusion

In conclusion, we explored how we get the SELECT COUNT GROUP BY equivalent in MongoDB with different examples. By using this statement, we can quickly and efficiently count the number of documents in a collection based on specific fields and conditions

More details about this topic can be found here.