CODEWITHSUNDEEP
mysqlmongodb
Yash
Yash

Reputation: 256

how to use sum, multiply, divide and group by aggregation in single mongodb query

I have following mysql query in which I have done sum on two different fields called "count" and "population" and then divided sum(count)/sum(population) and then multiplied it with 100000 and finally grouping it by year.

Select year, cname, (sum(count)/sum(population))*100000 as total from cancer c where c.cname ="lung and bronchus" group by year;

I have written following query in mongodb but I am not sure how to project 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 guide me in solving this query?

Upvotes: 5

Views: 11572

Answers (1)

wdberkeley
wdberkeley

Reputation: 11671

I'm not sure what you mean by "solving the query", but that query is not valid in its current form. I think you want a pipeline like the following:

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 that answer your question?

Upvotes: 11

Related Questions