MongoDB: How to Group By and Sum


You can use the following syntax to group by and count in MongoDB:

db.collection.aggregate([
    {$group : {_id:"$field_name1", count:{$sum:"$field_name2"}}}
])

Note that field_name1 is the field you’d like to group by and field_name2 is the field you’d like to sum.

The following examples show how to use this syntax with a collection teams with the following documents:

db.teams.insertOne({team: "Mavs", position: "Guard", points: 31})
db.teams.insertOne({team: "Spurs", position: "Forward", points: 22})
db.teams.insertOne({team: "Rockets", position: "Center", points: 19})
db.teams.insertOne({team: "Warriors", position: "Forward", points: 26})
db.teams.insertOne({team: "Cavs", position: "Guard", points: 33})

Example 1: Group By and Sum

We can use the following code to group by the ‘position’ field and count the sum of points for each position.

db.teams.aggregate([
    {$group : {_id:"$position", count:{$sum:"$points"}}}
])

This returns the following results:

{ _id: 'Forward', count: 48 }
{ _id: 'Guard', count: 64 }
{ _id: 'Center', count: 19 }

This tells us:

  • The players with position ‘Forward’ have a total of 48 points.
  • The players with position ‘Guard’ have a total of 64 points.
  • The players with position ‘Center’ have a total of 19 points.

Example 2: Group By and Sum (Then Sort)

We can use the following code to find the sum of points for each position and automatically sort the results in ascending order:

db.teams.aggregate([
    {$group : {_id:"$position", count:{$sum:"$points"}}},
    {$sort: {count:1}} 
])

This returns the following results:

{ _id: 'Center', count: 19 }
{ _id: 'Forward', count: 48 }
{ _id: 'Guard', count: 64 }

The results are sorted by points in ascending order (smallest to largest).

We can use -1 in the count argument to instead sort the results in descending order:

db.teams.aggregate([
    {$group : {_id:"$position", count:{$sum:"$points"}}},
    {$sort: {count:-1}} 
])

This returns the following results:

{ _id: 'Guard', count: 64 }
{ _id: 'Forward', count: 48 }
{ _id: 'Center', count: 19 }

Notice that the results are sorted by points in descending order (largest to smallest).

Note: You can find the complete documentation for $group here.

Additional Resources

The following tutorials explain how to perform other common operations in MongoDB:

MongoDB: How to Count Distinct Values in Field
MongoDB: How to Group By Multiple Fields
MongoDB: How to Group By and Count

Leave a Reply

Your email address will not be published.