Return the maximum sum value of the collection with MongoDB

1

I have collection in MongoDB and it has several records in it and I need to add a column by UF and then return the largest value of that sum. For example

UF: 'SP',
Valor: 10
----
UF: 'SP',
Valor: 23.5
----
UF: 'RJ',
Valor: 40
----
UF: 'PR',
Valor: 5

What I need to do is add the values by state, that is:

SP = 33.5
RJ = 40
PR = 5

And return the highest value in case:

40 (que é de RJ)

All this with mongoDB. I'm using PHP, but that does not interfere. The problem is with query I do not know how to do it correctly. I tried to make a similar one but it did not work, just returned the maximum number of each state:

{
   {
    '$group' : {
        '_id' : '$UF',
        'Maximo' : { 
            $max: {
                {'$sum' : '$Valor'}
            }
        }
    }
}

What I need is to return the maximum number of the sum, not all, only the largest value among all sums. This would like to do without having to give foreach in PHP, so I wanted to do everything in the query already.

    
asked by anonymous 10.12.2016 / 23:46

1 answer

1
  

To get the maximum value, regardless of the group:

db.Values.aggregate(
[        
    {
        $group : {
           _id :  null ,
           maximo: { $max: "$Valor" }
        }
    }
]    
)
  

To get the values by group (UF):

db.Values.aggregate(
[        
    {
        $group : {
           _id :  "$UF" ,
           total: { $sum: "$Valor" },           
           count: { $sum: 1 }           
        }
    }
]    
)
  

To get only the highest value from a given group:

db.Values.aggregate(
[        
    {
        $group : {
           _id :  "$UF" ,
           total: { $sum: "$Valor" },           
           count: { $sum: 1 }                     
        }
    },    
    { 
        $sort : { total : -1} 
    },
    { 
        $limit : 1        
    },
]    
)

References:

11.12.2016 / 02:03