Sum with MongoDB group by multiple columns to calculate total marks with duplicate ids

To sum marks with MongoDB group by multiple columns and calculate total marks with duplicate IDs, use the aggregate() method with $group stage. This allows grouping by multiple fields like ID and Name to sum marks for each unique combination.

Syntax

db.collection.aggregate([
    {
        $group: {
            _id: {
                field1: "$field1",
                field2: "$field2"
            },
            totalField: { $sum: "$fieldToSum" }
        }
    }
])

Create Sample Data

db.demo627.insertMany([
    {id: 101, "Name": "Chris", "Marks": 54},
    {id: 102, "Name": "Bob", "Marks": 74},
    {id: 101, "Name": "Chris", "Marks": 87},
    {id: 102, "Name": "Mike", "Marks": 70}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e9acb306c954c74be91e6b2"),
        ObjectId("5e9acb3c6c954c74be91e6b3"),
        ObjectId("5e9acb426c954c74be91e6b4"),
        ObjectId("5e9acb4b6c954c74be91e6b5")
    ]
}

Display Sample Data

db.demo627.find();

{ "_id": ObjectId("5e9acb306c954c74be91e6b2"), "id": 101, "Name": "Chris", "Marks": 54 }
{ "_id": ObjectId("5e9acb3c6c954c74be91e6b3"), "id": 102, "Name": "Bob", "Marks": 74 }
{ "_id": ObjectId("5e9acb426c954c74be91e6b4"), "id": 101, "Name": "Chris", "Marks": 87 }
{ "_id": ObjectId("5e9acb4b6c954c74be91e6b5"), "id": 102, "Name": "Mike", "Marks": 70 }

Group by Multiple Columns and Sum Marks

Calculate total marks for each unique combination of ID and Name ?

db.demo627.aggregate([
    {
        $group: {
            _id: {
                id: "$id",
                Name: "$Name"
            },
            TotalMarks: { $sum: "$Marks" }
        }
    }
]);

{ "_id": { "id": 101, "Name": "Chris" }, "TotalMarks": 141 }
{ "_id": { "id": 102, "Name": "Bob" }, "TotalMarks": 74 }
{ "_id": { "id": 102, "Name": "Mike" }, "TotalMarks": 70 }

Key Points

• The _id field in $group defines the grouping criteria using multiple fields.
• $sum operator calculates the total marks for each grouped combination.
• Documents with the same ID but different names are treated as separate groups.

Conclusion

Use $group with compound _id containing multiple fields to group by multiple columns. The $sum operator then calculates totals for each unique combination, handling duplicate IDs with different names as separate groups.