Summary: in this tutorial, you’ll learn how to use the MongoDB $sum to return the collective sum of numeric values. Table of Contents
Introduction to the MongoDB $sum
MongoDB $sum returns the sum of numeric values. Here’s the syntax of the $sum:
{ $sum: <expression> }
Code language: HTML, XML (xml)Typically, you apply the $sum to the numeric values. However, if a field contains a non-numeric value, the $sum ignores that value. Also, if the field doesn’t exist in any document, the $sum returns 0 for that field.
MongoDB $sum examples
We’ll use the following sales collection to demonstrate the $sum:
db.sales.insertMany([ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate("2022-01-15T08:00:00Z") }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate("2022-01-16T09:00:00Z") }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate("2022-01-16T09:05:00Z") }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate("2022-02-17T08:00:00Z") }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate("2022-02-18T21:06:00Z") }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate("2022-02-20T10:07:00Z") }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate("2022-02-21T10:08:00Z") }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate("2022-02-22T14:09:00Z") }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate("2022-02-23T14:09:00Z") }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate("2022-02-25T14:09:00Z")} ]);
Code language: JavaScript (javascript)1) A simple MongoDB $sum example
The following example calculates the total quantity of coffee sales in the sales collection:
db.sales.aggregate([ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]);
Code language: PHP (php)Output:
[ { _id: null, totalQty: 185 } ]
Code language: JavaScript (javascript)To remove the _id field from the output document, you can use the $project like this:
db.sales.aggregate([ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, { $project: { _id: 0 } }, ]);
Code language: PHP (php)Output:
[ { totalQty: 185 } ]
Code language: CSS (css)2) Using mongoDB $sum to calculcate the sum of groups
The following example uses the $sum to calculate the sum of quantity grouped by items:
db.sales.aggregate([ { $group: { _id: '$item', totalQty: { $sum: '$quantity' }, }, }, ]);
Output:
[ { _id: 'Cappuccino', totalQty: 49 }, { _id: 'Lattes', totalQty: 55 }, { _id: 'Americanos', totalQty: 70 }, { _id: 'Mochas', totalQty: 11 } ]
Code language: JavaScript (javascript)In this example, the $group groups the documents by items and the $sum returns the total quantity for each group.
3) Using the MongoDB $sum with $sort example
The following example uses the $sum to returns the total quantity of each item and sorts the result documents by the totalQty in descending order:
db.sales.aggregate([ { $group: { _id: '$item', totalQty: { $sum: '$quantity' }, }, }, { $sort: { totalQty: -1 } }, ]);
Code language: PHP (php)Output:
[ { _id: 'Americanos', totalQty: 70 }, { _id: 'Lattes', totalQty: 55 }, { _id: 'Cappuccino', totalQty: 49 }, { _id: 'Mochas', totalQty: 11 } ]
Code language: JavaScript (javascript)4) Using the MongoDB $sum with $match example
The following example uses the $sum to return the total quantity of each item and $match to include only items with a total quantity greater than 50:
db.sales.aggregate([ { $group: { _id: '$item', totalQty: { $sum: '$quantity' }, }, }, { $match: { totalQty: { $gt: 50 } } }, { $sort: { totalQty: -1 } }, ]);
Output:
[ { _id: 'Americanos', totalQty: 70 }, { _id: 'Lattes', totalQty: 55 } ]
Code language: JavaScript (javascript)5) Using the MongoDB $sum with an expression
The following example uses the $sum to calculate the total amount by multiplying price with quantity for coffee sales group by sizes:
db.sales.aggregate([ { $group: { _id: '$size', totalAmount: { $sum: { $multiply: ['$price', '$quantity'] } }, }, }, { $sort: { totalAmount: -1 } }, ]);
Code language: PHP (php)Output:
[ { _id: 'Tall', totalAmount: 1285 }, { _id: 'Grande', totalAmount: 875 }, { _id: 'Short', totalAmount: 182 } ]
Code language: JavaScript (javascript)Summary
- Use MongoDB $sum to return the collective sum of numeric values.
- The $sum ignores numeric values and return 0 for non-existing fields.
Was this tutorial helpful ?
How do I sum fields in MongoDB?
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 .
How can you group by a particular value in MongoDB?
We can group by single as well as multiple field from the collection, we can use $group operator in MongoDB to group fields from the collection and returns the new document as result. We are using $avg, $sum, $max, $min, $push, $last, $first and $addToSet operator with group by in MongoDB.
How does MongoDB calculate sum of salary?
$sum operator returns the sum of all numeric values of documents in the collection in MongoDB. Above will create collection (or table) (if collection already exists it will insert documents in it). Step 2.1 - We will group employee by firstName and find sum of salary of each group.