$sum
The $sum operator in Amazon DocumentDB returns the sum of the specified expression for each document in a group. It is a group accumulator operator that is typically used in the $group stage of an aggregation pipeline to perform summation calculations.
Parameters
Example (MongoDB Shell)
The following example demonstrates the use of the $sum operator to calculate the total sales for each product.
Create sample documents
db.sales.insertMany([
{ product: "abc", price: 10, quantity: 2 },
{ product: "abc", price: 10, quantity: 3 },
{ product: "xyz", price: 20, quantity: 1 },
{ product: "xyz", price: 20, quantity: 5 }
]);
Query example
db.sales.aggregate([
{ $group: {
_id: "$product",
totalSales: { $sum: { $multiply: [ "$price", "$quantity" ] } }
}}
]);
Output
[
{ "_id": "abc", "totalSales": 50 },
{ "_id": "xyz", "totalSales": 120 }
]
Window operator usage example (MongoDB Shell)
New from version 8.0.2.
The $sum operator can also be used as a window operator in the $setWindowFields stage. In this context, it returns the sum of the specified expression for the documents in each window. You specify the operator under the output field, and optionally define the window boundaries with a window document.
Create sample documents
db.dailySales.insertMany([
{ _id: 1, product: "abc", day: 1, amount: 10 },
{ _id: 2, product: "abc", day: 2, amount: 20 },
{ _id: 3, product: "abc", day: 3, amount: 15 },
{ _id: 4, product: "xyz", day: 1, amount: 30 },
{ _id: 5, product: "xyz", day: 2, amount: 25 }
]);
Query example
The following example partitions the documents by product, sorts each partition by day, and returns a running total of amount from the start of the partition through the current document.
db.dailySales.aggregate([
{
$setWindowFields: {
partitionBy: "$product",
sortBy: { day: 1 },
output: {
runningTotal: {
$sum: "$amount",
window: { documents: ["unbounded", "current"] }
}
}
}
}
]);
Output
[
{ "_id": 1, "product": "abc", "day": 1, "amount": 10, "runningTotal": 10 },
{ "_id": 2, "product": "abc", "day": 2, "amount": 20, "runningTotal": 30 },
{ "_id": 3, "product": "abc", "day": 3, "amount": 15, "runningTotal": 45 },
{ "_id": 4, "product": "xyz", "day": 1, "amount": 30, "runningTotal": 30 },
{ "_id": 5, "product": "xyz", "day": 2, "amount": 25, "runningTotal": 55 }
]
Each document is augmented with runningTotal, the cumulative sum of amount within its partition up to and including the current document.
Code examples
To view a code example for using the $sum operator, choose the tab for the language that you want to use. The following examples show both accumulator usage (in $group) and window operator usage (in $setWindowFields):
- Node.js
-
const { MongoClient } = require('mongodb');
async function example() {
const uri = 'mongodb://<username>:<password>@<cluster-endpoint>:27017/?tls=true&tlsCAFile=global-bundle.pem&replicaSet=rs0&readPreference=secondaryPreferred&retryWrites=false';
const client = new MongoClient(uri);
try {
await client.connect();
const db = client.db('test');
// Accumulator usage: sum per group
const sales = db.collection('sales');
await sales.insertMany([
{ product: "abc", price: 10, quantity: 2 },
{ product: "abc", price: 10, quantity: 3 },
{ product: "xyz", price: 20, quantity: 1 },
{ product: "xyz", price: 20, quantity: 5 }
]);
const accumulatorResult = await sales.aggregate([
{ $group: {
_id: "$product",
totalSales: { $sum: { $multiply: [ "$price", "$quantity" ] } }
}}
]).toArray();
console.log('Accumulator result:', accumulatorResult);
// Window operator usage: running total within each partition
const dailySales = db.collection('dailySales');
await dailySales.insertMany([
{ _id: 1, product: "abc", day: 1, amount: 10 },
{ _id: 2, product: "abc", day: 2, amount: 20 },
{ _id: 3, product: "abc", day: 3, amount: 15 },
{ _id: 4, product: "xyz", day: 1, amount: 30 },
{ _id: 5, product: "xyz", day: 2, amount: 25 }
]);
const windowResult = await dailySales.aggregate([
{
$setWindowFields: {
partitionBy: "$product",
sortBy: { day: 1 },
output: {
runningTotal: {
$sum: "$amount",
window: { documents: ["unbounded", "current"] }
}
}
}
}
]).toArray();
console.log('Window result:', windowResult);
} catch (error) {
console.error('Error:', error);
} finally {
await client.close();
}
}
example();
- Python
-
from pymongo import MongoClient
from pprint import pprint
def example():
client = None
try:
client = MongoClient('mongodb://<username>:<password>@<cluster-endpoint>:27017/?tls=true&tlsCAFile=global-bundle.pem&replicaSet=rs0&readPreference=secondaryPreferred&retryWrites=false')
db = client.test
# Accumulator usage: sum per group
sales = db.sales
sales.insert_many([
{ 'product': 'abc', 'price': 10, 'quantity': 2 },
{ 'product': 'abc', 'price': 10, 'quantity': 3 },
{ 'product': 'xyz', 'price': 20, 'quantity': 1 },
{ 'product': 'xyz', 'price': 20, 'quantity': 5 }
])
accumulator_result = list(sales.aggregate([
{ '$group': {
'_id': '$product',
'totalSales': { '$sum': { '$multiply': [ '$price', '$quantity' ] } }
}}
]))
pprint(accumulator_result)
# Window operator usage: running total within each partition
daily_sales = db.dailySales
daily_sales.insert_many([
{ '_id': 1, 'product': 'abc', 'day': 1, 'amount': 10 },
{ '_id': 2, 'product': 'abc', 'day': 2, 'amount': 20 },
{ '_id': 3, 'product': 'abc', 'day': 3, 'amount': 15 },
{ '_id': 4, 'product': 'xyz', 'day': 1, 'amount': 30 },
{ '_id': 5, 'product': 'xyz', 'day': 2, 'amount': 25 }
])
window_result = list(daily_sales.aggregate([
{
'$setWindowFields': {
'partitionBy': '$product',
'sortBy': { 'day': 1 },
'output': {
'runningTotal': {
'$sum': '$amount',
'window': { 'documents': ['unbounded', 'current'] }
}
}
}
}
]))
pprint(window_result)
except Exception as e:
print(f"An error occurred: {e}")
finally:
if client:
client.close()
example()
