11

I am looking to count all columns with the name amount in the documents that match my query,

 tickets.count({time: {$gte: a}, time: {$lte: tomorrow}}).then(function (numTickets) {

How can I get the total result of the document column called amount?

Example, if I do have:

{ time: 20, amount: 40}
{ time: 40, amount: 20}

it would return the total amount(60)?

Remember that I do need to use {time: {$gte: a}, time: {$lte: tomorrow} in the query.

How would I do this?

DAXaholic
  • 33,312
  • 6
  • 76
  • 74
maria
  • 207
  • 5
  • 22
  • 56

1 Answers1

27

Try it with the aggregation framework using the $match and $group operators, i.e. something like this 

db.tickets.aggregate([
    { $match: { time: {$gte: a, $lte: tomorrow} } },
    { $group: { _id: null, amount: { $sum: "$amount" } } }
])

for example with test data like this 

/* 1 */
{
    "_id" : ObjectId("57e0ed40828913a99c2ceb46"),
    "time" : 20,
    "amount" : 40
}

/* 2 */
{
    "_id" : ObjectId("57e0ed40828913a99c2ceb47"),
    "time" : 40,
    "amount" : 20
}

/* 3 */
{
    "_id" : ObjectId("57e0ed40828913a99c2ceb48"),
    "time" : 50,
    "amount" : 10
}

/* 4 */
{
    "_id" : ObjectId("57e0ed40828913a99c2ceb49"),
    "time" : 10,
    "amount" : 5
}

a pipeline (with dummy time range) like the following 

db.tickets.aggregate([
    { $match: { time: {$gte: 20, $lte: 40} } },
    { $group: { _id: null, amount: { $sum: "$amount" } } }
])

would give you a result like this 

/* 1 */
{
    "_id" : null,
    "amount" : 60
}

Pipeline in action

DAXaholic
  • 33,312
  • 6
  • 76
  • 74
  • will that work with mongoose too? cause tickets.aggregate([{ $match: { time: {$gte: a, $lte: tomorrow} } }, { $group: { _id: null, amount: { $sum: "$amount" } } }]).then(function (test) { wont work for me, in the terms its not executing it. – maria Sep 20 '16 at 08:15
  • tickets is my mongoose model yes. Would you take a look on teamviewer? , anyway, i will take a look, and accept your answer :) – maria Sep 20 '16 at 08:19
  • Your callback should have two parameters (for error and result) I guess. [This](http://stackoverflow.com/questions/26623141/mongoose-aggregate-and-sort) SO question shows how to use aggregate pipeline with mongoose - hope that helps. Otherwise I will try to test it myself tonight – DAXaholic Sep 20 '16 at 08:23
  • will continue to get it work. im console.logging inside the result, but its never excecuted. – maria Sep 20 '16 at 08:25
  • @DAXaholic i have amount with two decimals points stored as a string how i can get the sum of all records using mongose...? – Muhammad Usama Mashkoor May 30 '19 at 13:50