CODEWITHSUNDEEP
mongodb
Matt Lightbourn
Matt Lightbourn

Reputation: 597

Mongodb group and sum not working

I have an aggregation script which gets me to a result set which looks like below set:

{ 
"_id" : "SI-3", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-04-14T00:00:00.201+0000"), 
"transaction_net_value" : 1666.67, 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(10), 
"item_name" : "Product Three", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : 1666.67
}
{ 
"_id" : "SI-2", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-05-19T00:00:00.201+0000"), 
"transaction_net_value" : NumberInt(1500), 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(1), 
"item_name" : "Product One", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : NumberInt(1500)
}
{ 
"_id" : "SI-15", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-07-11T00:00:00.201+0000"), 
"transaction_net_value" : 8333.33, 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(7), 
"item_name" : "Product Four", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : 8333.33
}
{ 
"_id" : "SI-14", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-07-11T00:00:00.201+0000"), 
"transaction_net_value" : NumberInt(7500), 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(6), 
"item_name" : "Product Four", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : NumberInt(7500)
}
{ 
"_id" : "SI-1", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-06-16T00:00:00.201+0000"), 
"transaction_net_value" : NumberInt(1500), 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(1), 
"item_name" : "Product One", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : NumberInt(1500)
}
{ 
"_id" : "SI-1", 
"party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
"transaction_date" : ISODate("2016-06-16T00:00:00.201+0000"), 
"transaction_net_value" : NumberInt(1500), 
"object_category" : "Revenue", 
"object_origin_category" : "Bookkeeping", 
"object_origin" : "Sage One", 
"item_quantity" : NumberInt(1), 
"item_name" : "Product Twenty Two", 
"item_category" : "Sales Revenue", 
"item_ net_total_value" : NumberInt(1500)
}

When I add a final stage as shown below, the results I get does not $sum as expected. I get 0 when it's quite obviously not 0. Any ideas what I've done wrong in my $group stage?

$group : {
_id: {"party_uuid" : "$party_uuid", 
  "transaction_year" : {$year : "$transaction_date"}, 
  "transaction_month" : {$month : "$transaction_date"}, 
  "category" : "$item_category", 
  "object_origin_category" : "$object_origin_category", 
  "object_origin" : "$object_origin"},
  "month_net_value" : { $sum: "$item_net_total_value" }
}

This results

{ 
"_id" : {
    "party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
    "transaction_year" : NumberInt(2016), 
    "transaction_month" : NumberInt(6), 
    "category" : "Sales Revenue", 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"month_net_value" : NumberInt(0)
}
{ 
"_id" : {
    "party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
    "transaction_year" : NumberInt(2016), 
    "transaction_month" : NumberInt(5), 
    "category" : "Sales Revenue", 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"month_net_value" : NumberInt(0)
}
{ 
"_id" : {
    "party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
    "transaction_year" : NumberInt(2016), 
    "transaction_month" : NumberInt(7), 
    "category" : "Sales Revenue", 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"month_net_value" : NumberInt(0)
}
{ 
"_id" : {
    "party_uuid" : "dfa1e80a-5521-11e6-beb8-9e71128cae77", 
    "transaction_year" : NumberInt(2016), 
    "transaction_month" : NumberInt(4), 
    "category" : "Sales Revenue", 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"month_net_value" : NumberInt(0)
}

Thanks again

Upvotes: 2

Views: 341

Answers (1)

Jan B.
Jan B.

Reputation: 6450

Could it be that you have a whitespace in your key:

"item_ net_total_value"

Compare with:

"$item_net_total_value"

Upvotes: 2

Related Questions