How do I sum all fields in MongoDB?

The MongoDB sum query operation (aggregation) is simply used to calculate and return the collective sum of numeric values. It is important to note that the operation ignores non-numeric values. Moreover, if a non-existent field is passed, the operator returns a 0 value.

The behavior of MongoDB sum query was changed in MongoDB 5.0 and is now made available with several other operations or stages. In MongoDB 3.2 and earlier, it is only available in the $group stage.

This guide aims to provide examples so you can understand how to use the MongoDB sum query operator. So, let us get this quick and easy guide started.

The MongoDB Sum Query Syntax

The syntax for the $sum operator varies on the basis of the number of expressions a user wants to operate on.

For a single operand expression:

{ $sum:  }

For a list of multiple operand expressions:

{ $sum: [ ,  ... ]  }

Result Data Type

Except where it cannot be adequately represented in that type, the outcome will be of the same type as the input. In these instances:

• If the result is representable as a 64-bit integer, a 32-bit integer will be changed to a 64-bit integer.
• If the result cannot be represented as a 64-bit integer, a 32-bit integer will be transformed to a double.
• If the result cannot be represented as a 64-bit integer, it will be changed to a double.

Using the MongoDB Sum Query in the Mongo Shell

Let us grab a few examples from the official docs to understand how to use the MongoDB sum query operation.

I am new to mongodb and may be I am missing something. But having a lot of samples in internet, still having problems to get a total on one field which is part of array of objects. Here is what I am doing:

db.collection.insertMany([
{ "id" : "6002010011500", "balance" : [ { "type" : "PR", "amount" : "1000" 
}, { "type" : "IN", "amount" : "300" } ] },
{"id" :  "5001010001005", "balance": [ { "type" : "PR", "amount" : "-3000" 
}, { "type" : "IN", "amount" : "-600" } ] }
])

trying to get total amount in different ways:

db.collection.aggregate([
{$group:{_id:null, TotalBalance:{$sum:"$balance.amount"}}}
])

getting the balance 0 instead of -2300

{ "_id" : null, "TotalBalance" : 0 }

same things with $unwind:

db.collection.aggregate([
{$unwind:"$balance"},
{$group:{_id:null, TotalBalance:{$sum:"$balance.amount"}}}
])

what I am doing wrong?

Thanks

In this article, we are going to talk about sum, an aggregation method in databases. With the help of the sum method, you can sum up large sets of numbers and calculate totals that can help in reports and other calculations. Let’s get straight into showing you how to perform a MongoDB sum.

What is the use of Sum in MongoDB and how to use it?

The $sum operator is used to sum up the values of fields in documents.

Today, I’m going to explain this how to perform a sum with an example. So let’s get started without wasting time! First of all, we are going to create a collection with some documents:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

>db.aggregateSumDemo.insertOne({"CustomerName":"Larry","Amount":140});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8baa0680f10143d8431e18")
}
>db.aggregateSumDemo.insertOne({"CustomerName":"Mike","Amount":160});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8baa1380f10143d8431e19")
}
>db.aggregateSumDemo.insertOne({"CustomerName":"Sam","Amount":300});{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8baa1c80f10143d8431e1a")
}
>db.aggregateSumDemo.insertOne({"CustomerName":"David","Amount":500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8baa2580f10143d8431e1b")
}

If you want to see all the documents that you have added in the collection then you have to use find() method. Here is the query that you would use:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

> db.aggregateSumDemo.find().pretty();
Once you hit enter, it will show up results on as shown below:
{
   "_id" : ObjectId("5c8baa0680f10143d8431e18"),
   "CustomerName" : "Larry",
   "Amount" : 140
}
{
   "_id" : ObjectId("5c8baa1380f10143d8431e19"),
   "CustomerName" : "Mike",
   "Amount" : 160
}
{
   "_id" : ObjectId("5c8baa1c80f10143d8431e1a"),
   "CustomerName" : "Sam",
   "Amount" : 300
}
{
   "_id" : ObjectId("5c8baa2580f10143d8431e1b"),
   "CustomerName" : "David",
   "Amount" : 500
}

To give you a better overview of it we are going to show you two use cases. In case 1, we will sum up the total number of records.

Case 1

Here is the query in MongoDB to get the records count:

> db.aggregateSumDemo.aggregate([ {
   ... $group: {
      ... _id: null,
      ... "TotalCount": {
         ... $sum:1
      ... }
   ... }
... } ] );

Case 1 Result

{ "_id" : null, "TotalCount" : 4 }

Here is the query to aggregate sum in MongoDB to get the total sum. You can see in the results that after running the query we got the total number of available records which is 4. Let’s go with the other example where we sum up the amount:

Case 2 − Here is the query to sum the amount

> db.aggregateSumDemo.aggregate([ {
   ... $group: {
      ... _id: null,
      ... "TotalAmount": {
         ... $sum: "$Amount"
      ... }
   ... }
... } ] );

Case 2 Result

{ "_id" : null, "TotalAmount" : 1100 }

When we sum the amounts of all the records then you can see the total amount as shown above.

Conclusion

I hope you now understand how to use $sum operator in MongoDB. The $sum operator should come in handy in both basic and complex queries. We hope you learned what you needed to apply it to your specific problem. Thanks for your time.

How do I sum a column in MongoDB?

How to do calculations in MongoDB?

What is $sum 1 in MongoDB?

How to add numbers in MongoDB?