$sum (aggregation)
On this page
Definition
$sum
Calculates and returns the sum of numeric values.
$sum
ignores non-numeric values.$sum
is available in the following stages:$addFields
(Available starting in MongoDB 3.4)$set
(Available starting in MongoDB 4.2)$replaceRoot
(Available starting in MongoDB 3.4)$replaceWith
(Available starting in MongoDB 4.2)
Compatibility
You can use $sum
for deployments hosted in the following
environments:
MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud
MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB
Syntax
When used in the $group
stage, $sum
has the
following syntax and returns the collective sum of all the numeric
values that result from applying a specified expression to each
document in a group of documents that share the same group by key:
{ $sum: <expression> }
When used in the other supported stages, $sum
returns the
sum of the specified expression or list of expressions for each
document and has one of two syntaxes:
$sum
has one specified expression as its operand:{ $sum: <expression> } $sum
has a list of specified expressions as its operand:{ $sum: [ <expression1>, <expression2> ... ] }
For more information on expressions, see Expressions.
Behavior
Result Data Type
The result will have the same type as the input except when it cannot be represented accurately in that type. In these cases:
A 32-bit integer will be converted to a 64-bit integer if the result is representable as a 64-bit integer.
A 32-bit integer will be converted to a double if the result is not representable as a 64-bit integer.
A 64-bit integer will be converted to double if the result is not representable as a 64-bit integer.
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 in the collection,
$sum
returns 0
for that field.
If all operands are non-numeric, $sum
returns 0
.
Example | Field Values | Results |
---|---|---|
{ $sum : <field> } | Numeric | Sum of Values |
{ $sum : <field> } | Numeric and Non-Numeric | Sum of Numeric Values |
{ $sum : <field> } | Non-Numeric or Non-Existent | 0 |
Array Operand
In the $group
stage, if the expression resolves to an
array, $sum
treats the operand as a non-numerical 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 numerical 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-numerical value.
Examples
Use in $group
Stage
Consider a sales
collection with the following documents:
{ "_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") }
Grouping the documents by the day and the year of the date
field,
the following operation uses the $sum
accumulator to compute the
total amount and the count for each group of documents.
db.sales.aggregate( [ { $group: { _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } }, totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }, count: { $sum: 1 } } } ] )
The operation returns the following results:
{ "_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 }
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 the $sum
in the
$project
stage to calculate the total quiz scores, the
total lab scores, and the total of the final and the midterm:
db.students.aggregate([ { $project: { quizTotal: { $sum: "$quizzes"}, labTotal: { $sum: "$labs" }, examTotal: { $sum: [ "$final", "$midterm" ] } } } ])
The operation results in the following documents:
{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 } { "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 } { "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }
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 numerical 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-numerical value.