I have a collection with documents that contain fields type, totalA and totalB
I want to use the aggregation framework in order to group by type - and get the sum of both totalA and totalB together.
The last thing I tried (doesn't work) is:
'$group' : {
'_id' : '$type',
'totalA' : { '$sum' : '$totalA' },
'totalB' : { '$sum' : '$totalB' },
'totalSum' : { '$sum' : '$totalA', '$sum' : '$totalB' },
} }
totalSum has the sum of only one of the fields instead of the combined value.
I found a solution:
Just using $project to $add the two fields together in the output.
{ "$project" : {
'totalA' : '$totalA',
'totalB' : '$totalB',
'totalSum' : { '$add' : [ '$totalA', '$totalB' ] },
}
You can use $sum in this way:
{
$group : {
_id: null,
amount: { $sum: { $add : [
'$NumberOfItemsShipped', '$NumberOfItemsUnshipped'
]}},
}
},
In addition to this answer here, had issue with $add giving wrong values, when keys are not present in some documents.
If, for example, Col3 & Col4 keys are sometimes not present/ undefined, then below $ifNull will help:
{
$group : {
_id: {
"Col1": "$Col1",
"Col2": "$Col2"
},
sum_of_all_days_in_year: {
$sum: {
"$add": [
{ "$ifNull": ["$Col3", 0] },
{ "$ifNull": ["$Col4", 0] }
]
}
},
}
}
