$sum (accumulator operator)

Result Data Type

When input types are mixed, $sum promotes the smaller input type to the larger of the two. A type is considered larger when it represents a wider range of values. The order of numeric types from smallest to largest is: integer → long → double → decimal

The larger of the input types also determines the result type unless the operation overflows and is beyond the range represented by that larger data type. In cases of overflow, $sum promotes the result according to the following order:

• If the larger input type is integer, the result type is promoted to long.

• If the larger input type is long, the result type is promoted to double.

• If the larger type is double or decimal, the overflow result is represented as + or - infinity. There is no type promotion of the result.

Non-Numeric or Non-Existent Fields

If used on a field that contains both numeric and non-numeric values, $sum ignores the non-numeric values and returns the sum of the numeric values.

If used on a field that does not exist in any document, $sum returns 0 for that field.

If all operands are non-numeric, non-arrays, or contain null values, $sum returns 0. For details on how $sum handles arrays, see Array Operand.

Array Operand

In the $group stage, if the expression resolves to an array, $sum treats the operand as a non-numeric value.

In the other supported stages:

• With a single expression as its operand, if the expression resolves to an array, $sum traverses into the array to operate on the numeric elements of the array to return a single value.

• With a list of expressions as its operand, if any of the expressions resolves to an array, $sum does not traverse into the array but instead treats the array as a non-numeric value.

For example, when not used in a $group stage:

• If the $sum operand is [ 2, 2 ], $sum adds the array elements and returns 4.

• If the $sum operand is [ 2, [ 3, 4 ] ], $sum returns 2 because it treats the nested array [ 3, 4 ] as a non-numeric value.

Use in $group Stage

Consider a sales collection with the following documents:

db.sales.insertMany( [
   { "_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 following operation groups documents by day and year of the date field, then uses $sum to compute the total amount and count for each group:

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

The operation returns:

{ "_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 $sum on a non-existent field returns a value of 0. The following operation attempts to $sum on qty:

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

The operation returns:

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 0, "count" : 1 }

You can use the $count accumulator in place of { $sum : 1 } in the $group stage.

Use in $project Stage

A collection students contains the following documents:

{ "_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 following example uses $sum in the $project stage to calculate total quiz scores, total lab scores, and the combined final and midterm score:

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

The operation returns:

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

Use in $setWindowFields Stage

Create a cakeSales collection that contains cake sales in the states of 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 }
] )

This example uses $sum in the $setWindowFields stage to output the sum of the quantity of cakes sold in each state:

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

In the example:

• partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.

• sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.

• output sets sumQuantityForState to the sum of quantity values in a documents window that spans from the beginning of the partition (unbounded) to the current document.

In this output, the sumQuantityForState field shows the running sum of quantity values for CA and WA:

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