MongoDB SELECT COUNT GROUP BY?

To perform a SELECT COUNT GROUP BY operation in MongoDB, use the $group stage in the aggregation pipeline with the $sum accumulator to count documents grouped by a specific field.

Syntax

db.collection.aggregate([
    { $group: { _id: "$fieldName", count: { $sum: 1 } } }
]);

Sample Data

Let's create a collection with student records to demonstrate counting by StudentId ?

db.countGroupByDemo.insertMany([
    {"StudentId": 10, "StudentName": "John"},
    {"StudentId": 10, "StudentName": "Carol"},
    {"StudentId": 20, "StudentName": "Sam"},
    {"StudentId": 30, "StudentName": "Mike"},
    {"StudentId": 30, "StudentName": "David"},
    {"StudentId": 10, "StudentName": "Maxwell"},
    {"StudentId": 20, "StudentName": "Bob"}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("...")
    ]
}

Display All Documents

db.countGroupByDemo.find().pretty();

{
    "_id": ObjectId("5c7700871e9c5dd6f1f78296"),
    "StudentId": 10,
    "StudentName": "John"
}
{
    "_id": ObjectId("5c77008f1e9c5dd6f1f78297"),
    "StudentId": 10,
    "StudentName": "Carol"
}
{
    "_id": ObjectId("5c7700971e9c5dd6f1f78298"),
    "StudentId": 20,
    "StudentName": "Sam"
}
{
    "_id": ObjectId("5c7700a21e9c5dd6f1f78299"),
    "StudentId": 30,
    "StudentName": "Mike"
}
{
    "_id": ObjectId("5c7700aa1e9c5dd6f1f7829a"),
    "StudentId": 30,
    "StudentName": "David"
}
{
    "_id": ObjectId("5c7700b41e9c5dd6f1f7829b"),
    "StudentId": 10,
    "StudentName": "Maxwell"
}
{
    "_id": ObjectId("5c7700bd1e9c5dd6f1f7829c"),
    "StudentId": 20,
    "StudentName": "Bob"
}

Count Documents Grouped by StudentId

Use the aggregation framework to count how many documents exist for each StudentId ?

db.countGroupByDemo.aggregate([
    { $group: { _id: "$StudentId", counter: { $sum: 1 } } }
]);

{ "_id": 30, "counter": 2 }
{ "_id": 20, "counter": 2 }
{ "_id": 10, "counter": 3 }

Key Points

• $group groups documents by the specified field (_id: "$StudentId")
• $sum: 1 counts each document in the group (equivalent to SQL's COUNT(*))
• The _id field in the result contains the grouped value

Conclusion

MongoDB's aggregation pipeline with $group and $sum: 1 provides the equivalent functionality of SQL's SELECT COUNT(*) GROUP BY operation, allowing you to count documents grouped by any field.