Mongodb Aggregation Query with $sum

Question

I have a collection called test. I want to get total amount of one key: sal across all documents present in the test collection.

Here is sample document:

[
    {
    "name": "hari",
    "sal": "100",
    "status": "Y",
    "address": "Bangalore"
    },
    {
    "name": "Sam",
    "sal": "200",
    "status": "Y",
    "address": "Bangalore"
    },
    {
    "name": "Nik",
    "sal": "200",
    "status": "N",
    "address": "Bangalore"
    }
]

and I tried the following query:

db.getCollection('test').aggregate([{
  $match: {
    Status: "Y"
  }
}, {
$group: {
    _id: null,
    total: {
        $sum: "$sal"
    }
  }
}])

I am getting zero.

Can anyone suggest me the right query to add a particular key across all the documents in a collection.

possible duplicate of How to aggregate sum in MongoDB to get a total count? — Neo-coder
– Neo-coder, Commented Aug 20, 2015 at 10:29
It would sound like your sal field is actually a string, or at least not a number, or non-existant. — Blakes Seven
– Blakes Seven, Commented Aug 20, 2015 at 10:42
Your query looks correct at first glance but it might not fit your documents. Please post an example document. — Philipp
– Philipp, Commented Aug 20, 2015 at 10:46
Hi Philipp, The below are some sample document of test collections ******* test { "name": "hari", "sal":"100", "status": "Y", "address": "Bangalore" } { "name": "Sam", "sal":"200", "status": "Y", "address": "Bangalore" } { "name": "Nik", "sal":"200", "status": "N", "address": "Bangalore" } while run the aggregation query db.getCollection('test').aggregate([{$match:{status:"Y"}},{$group:{_id:null,total:{$sum:"$sal"}}}]). I am getting zero value. — Sanjayakumar Sahu
– Sanjayakumar Sahu, Commented Aug 20, 2015 at 11:33
As per your document structure sal looks like string data type, so either you should changed sal data type to Int or Float for more how to convert data type check this link — Neo-coder
– Neo-coder, Commented Aug 20, 2015 at 12:22

Community · Accepted Answer · 2017-05-23 12:07:53Z

1

As mentioned in the sample document,

$sum only works with ints, longs and floats. Right now, there is no operator to parse a string into a number, although that would be very useful. You can do this yourself as is described in Mongo convert all numeric fields that are stored as string Mongo convert all numeric fields that are stored as string

You can refer the below answer for further details:- MongoDB - Aggregate Sum

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Aug 20, 2015 at 12:04

Nishant

3,7141 gold badge22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anand Mishra Over a year ago

Use this totalAmount: $group: { totalAmount: { $sum: { $multiply: [ "$sal", "$quantity" ] } } }

xiansweety Over a year ago

$multiply only supports numeric types, not String

Collectives™ on Stack Overflow

Mongodb Aggregation Query with $sum

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related