How to use sum, multiply, divide and group by aggregation in one mongodb query
I have the following mysql query where I executed the sum on two different fields called "count" and "population" and then divided the sum (count) / sum (population) and then multiplied it by 100000 and finally grouped her over the years.
Select year, cname, (sum(count)/sum(population))*100000 as total from cancer c where c.cname ="lung and bronchus" group by year;
I wrote the following query in mongodb, but I'm not sure how to design the cname and year.
db.cancer_stats.aggregate([
{$match:{cname: "lung and bronchus"}},
{$group:{_id:"year"},
{total:{$multiply:[$divide:[$sum:"$count", $sum:"population"], 100000]}}
}])
can anyone help me with this issue?
+3
source to share
1 answer
I'm not sure what you mean by "request resolution", but this request is not valid in its current form. I think you need a pipeline like below:
db.cancer_stats.aggregate([
{ "$match" : { "cname" : "lung and bronchus" } },
{ "$group" : { "_id" : "year", "t_count" : { "$sum" : "$count" }, "t_population" : { "$sum" : "$population" } } },
{ "$project" : { "result" : { "$multiply" : [100000, { "$divide" : ["$t_count", "$t_population"] } ] } } }
])
Does this answer your question?
+6
source to share