CODEWITHSUNDEEP
mongodbsummax
Avi
Avi

Reputation: 73

Max of sum in mongodb

The query below gives me the sum of models in each year. I need to find the year when maximum models were made. (data is another array in manufacturer)

db.structure.aggregate([
  {$unwind : "$manufacturers"},
  {$group: {
    _id: { day:"$_id.year" },
    count: { $sum: "$manufacturers.data.modelName" }
  }},
  {$sort: {count : -1}},
  {$out : "out"}
]);

Upvotes: 1

Views: 2529

Answers (1)

krishna
krishna

Reputation: 499

  1. Group by Year
  2. Count total number of Models made in that year
  3. Sort according to sum of Models
  4. Limit the query to 1

Now you will get the year when maximum models were made.

So your Query(Java implementation) will look like below:

     // Group by Year
     DBObject groupFields = new BasicDBObject( "_id", "$_id.year");

     //Count total no of models made in that year
     groupFields.put("sum", new BasicDBObject( "$sum", "$manufacturers.data.modelName"));

     //Group the fields
     DBObject group = new BasicDBObject("$group", groupFields);

     //Sort in descending order inorder to get maximum models 
     DBObject sortFields = new BasicDBObject("sum", -1);
     DBObject sort = new BasicDBObject("$sort", sortFields );

     //Limit to one since you need the year when maximum models were made
     DBObject limit = new BasicDBObject("$limit", 1 );
     AggregationOutput output = myColl.aggregate(group,sort,limit);

Hope this helps.

Editing your input:

db.structure.aggregate([
{$unwind : "$manufacturers"},
    {$group: {
        _id: {
            day:"$_id.year"
        },
        count: { $sum: "$manufacturers.data.modelName" }
    }},
    {$sort: {count : -1}},
    //Limit by 1 to get year when maximum models where made
    {$limit : 1},
    {$out : "out"}
]);

Upvotes: 1

Related Questions