MongoDB Sum Query – Comprehensive Guide

In this guide, I will cover the MongoDB sum query operation using the $sum operator.

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: <expression> }

For a list of multiple operand expressions:

{ $sum: [ <expression1>, <expression2> ... ]  }

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.

Using in the $group stage

• Let us consider a sales collection with the below documents for the example.

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }

• The next action, which groups the documents by the date field’s day and year, utilizes the $sum accumulator to determine the total amount and count for each set of documents.

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
           totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
           count: { $sum: 1 }
         }
     }
   ]
)

• Here’s what we get as output:

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }

Using in the $project stage:

• Let us consider a students collection with the below documents for this example:

{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 }
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 }
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }

• The $sum in the $project stage is used in the following example to determine the total quiz scores, total lab scores, and the total of the final and midterm:

db.students.aggregate([
   {
     $project: {
       quizTotal: { $sum: "$quizzes"},
       labTotal: { $sum: "$labs" },
       examTotal: { $sum: [ "$final", "$midterm" ] }
     }
   }
])

• Here’s what the output looks like:

{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 }
{ "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 }
{ "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }

Using in the $setWindowFields stage

• We will first create a collection called cakeSales that comprises of documents representing sales in California (CA) and Washington (WA):

db.cakeSales.insertMany( [
   { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"),
     state: "CA", price: 13, quantity: 120 },
   { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"),
     state: "WA", price: 14, quantity: 140 },
   { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"),
     state: "CA", price: 12, quantity: 145 },
   { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"),
     state: "WA", price: 13, quantity: 104 },
   { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"),
     state: "CA", price: 41, quantity: 162 },
   { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"),
     state: "WA", price: 43, quantity: 134 }
] )

• In this example, $sum is used in the $setWindowFields step to report the total number of cakes sold in each state:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            sumQuantityForState: {
               $sum: "$quantity",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            }
         }
      }
   }
] )

Notes:

• partitionBy: “$state” divides the collection’s documents by state. CA and WA have their own divisions.
• sortBy: orderDate: 1 sorts the documents in each partition in ascending order (1) by orderDate, with the earliest orderDate at the top.
• output computes the sum of the quantity values using $sum in a documents window and assigns it to the sumQuantityForState field.

Read More: stats() Function in MongoDB – Easy 2021 Guide

Conclusion

Learn to use the MongoDB sum query operation (aggregation) in the mongo shell.

Noteworthy References

https://docs.mongodb.com/manual/reference/operator/aggregation/sum/