In MongoDB, the $sum aggregation pipeline operator calculates and returns the sum of numeric values.
Syntax
The $sum operator supports two syntaxes.
Syntax 1:
{ $sum: <expression> }Syntax 2:
{ $sum: [ <expression1>, <expression2> ... ] }The first syntax accepts one argument and the second syntax accepts multiple arguments.
When used in the $group stage, you can only use the first syntax. In this case, $sum returns the collective sum of all the numeric values that result from applying the specified expression to each document in a group of documents that share the same group by key.
Examples of Syntax 1 (Single Argument)
Here are a couple of examples that use syntax 1.
Grouped Documents
This example uses $sum in conjunction with $group to return the sum across a group of documents that are grouped by key.
Suppose we have a collection called pets with the following documents:
{ "_id" : 1, "name" : "Wag", "type" : "Dog", "weight" : 20 }
{ "_id" : 2, "name" : "Bark", "type" : "Dog", "weight" : 10 }
{ "_id" : 3, "name" : "Meow", "type" : "Cat", "weight" : 7 }
{ "_id" : 4, "name" : "Scratch", "type" : "Cat", "weight" : 8 }
{ "_id" : 5, "name" : "Bruce", "type" : "Kangaroo", "weight" : 100 }
{ "_id" : 6, "name" : "Hop", "type" : "Kangaroo", "weight" : 130 }
{ "_id" : 7, "name" : "Punch", "type" : "Kangaroo", "weight" : 200 }
{ "_id" : 8, "name" : "Snap", "type" : "Cat", "weight" : 12 }
{ "_id" : 9, "name" : "Ruff", "type" : "Dog", "weight" : 30 }
We can group these documents by their type field, and then use $sum to return the sum of the weight field for each group:
db.pets.aggregate(
[
{
$group:
{
_id: "$type",
sum: { $sum: "$weight" }
}
}
]
)Result:
{ "_id" : "Kangaroo", "sum" : 430 }
{ "_id" : "Cat", "sum" : 27 }
{ "_id" : "Dog", "sum" : 60 }
Arrays
This example applies $sum to a single document that contains a field with an array of values.
This option is only available when using the single argument syntax. Arrays are ignored when using the multi-argument syntax (more on this below).
Suppose we have a collection called players with the following documents:
{ "_id" : 1, "player" : "Homer", "scores" : [ 1, 7, 2, 3, 8, 7, 1 ] }
{ "_id" : 2, "player" : "Marge", "scores" : [ 0, 1, 8, 17, 18, 8 ] }
{ "_id" : 3, "player" : "Bart", "scores" : [ 15, 11, 8, 0, 1, 3 ] }
{ "_id" : 4, "player" : "Brian", "scores" : [ 7 ] }
{ "_id" : 5, "player" : "Farnsworth", "scores" : [ ] }
{ "_id" : 6, "player" : "Meg", "scores" : null }
{ "_id" : 7, "player" : "Ron" }
We can apply $sum to the scores field in each document:
db.players.aggregate(
[
{
$project:
{
player: 1,
sum: { $sum: "$scores" }
}
}
]
)Result:
{ "_id" : 1, "player" : "Homer", "sum" : 29 }
{ "_id" : 2, "player" : "Marge", "sum" : 52 }
{ "_id" : 3, "player" : "Bart", "sum" : 38 }
{ "_id" : 4, "player" : "Brian", "sum" : 7 }
{ "_id" : 5, "player" : "Farnsworth", "sum" : 0 }
{ "_id" : 6, "player" : "Meg", "sum" : 0 }
{ "_id" : 7, "player" : "Ron", "sum" : 0 }
In this case, the first four documents returned the sum of the various numbers that were in their respective arrays.
In document 4’s case, this was the same as the number, because there was only one number in the array.
Document 5 returned 0 because we provided an empty array.
Document 6 returned 0 because we provided null as the argument.
Document 7 returned 0 because the field didn’t even exist.
Example of Syntax 2 (Multiple Arguments)
The second syntax involves providing $sum with more than one argument. $sum then calculates the sum based on all of the supplied arguments.
Suppose we have a collection called data with the following documents:
{ "_id" : 1, "a" : 1, "b" : 2, "c" : 3, "d" : 4 }
{ "_id" : 2, "a" : 1, "b" : 2, "c" : 3, "d" : [ 4 ] }
{ "_id" : 3, "a" : 1, "b" : 2, "c" : 3, "d" : "Hey" }
{ "_id" : 4, "a" : "One", "b" : "Two", "c" : "Three", "d" : "Four" }
We can use $sum to return the sum of the a, b, c, and d fields of each document:
db.data.aggregate(
[
{
$project:
{
sum: { $sum: [ "$a", "$b", "$c", "$d" ] }
}
}
]
)Result:
{ "_id" : 1, "sum" : 10 }
{ "_id" : 2, "sum" : 6 }
{ "_id" : 3, "sum" : 6 }
{ "_id" : 4, "sum" : 0 }
Document 1 returns the sum of the input values of 1, 2, 3, and 4.
However, the next two documents only returned the sum of the input values of 1, 2, and 3. The $sum operator ignored their d fields.
This is because $sum ignores non-numeric values. So in this case it ignored "Hey" in document 3 and calculated the sum from the remaining (numeric) fields.
As for document 2, its d field contains an array. As mentioned, the $sum operator ignores arrays when using the multi-argument syntax. More precisely, it treats arrays as non-numerical values when used in this context, and $sum ignores non-numerical values.
If all values are non-numeric, then $sum returns 0. We can see this with document 4.
Missing Fields
When using the multi-argument syntax, $sum ignores any missing fields. That is, if you supply a field that doesn’t exist, it ignores it. If none of the fields exist, then it returns 0.
Example:
db.data.aggregate(
[
{
$project:
{
sum: { $sum: [ "$a", "$b", "$c", "$d", "$e" ] }
}
}
]
)Result:
{ "_id" : 1, "sum" : 10 }
{ "_id" : 2, "sum" : 6 }
{ "_id" : 3, "sum" : 6 }
{ "_id" : 4, "sum" : 0 }
In this case I provided an extra field ($e) that doesn’t exist in the documents. $sum calculated the sum based on the remaining fields that do exist.
However, here’s what happens when none of the fields exist:
db.data.aggregate(
[
{
$project:
{
result: { $sum: [ "$x", "$y", "$z" ] }
}
}
]
)Result:
{ "_id" : 1, "result" : 0 }
{ "_id" : 2, "result" : 0 }
{ "_id" : 3, "result" : 0 }
{ "_id" : 4, "result" : 0 }
The result is 0 for all documents.
As we saw earlier, when using the single-argument syntax, a missing field results in 0.
Example:
db.pets.aggregate(
[
{
$group:
{
_id: "$type",
sum: { $sum: "$oops!" }
}
}
]
)Result:
{ "_id" : "Cat", "sum" : 0 }
{ "_id" : "Dog", "sum" : 0 }
{ "_id" : "Kangaroo", "sum" : 0 }
Available Stages
$sum is available in the in the following stages:
$group$project$addFields$set$replaceRoot$replaceWith$matchstage that includes an$exprexpression