Return the maximum sum of Collection with Mongodb

Question

I have a collection in the MongoDB and it has several records and I need to sum up a column by UF and then return the highest 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 the state, that is to say:

SP = 33.5
RJ = 40
PR = 5

And return the higher value than in the case:

40 (que é de RJ)

All this with mongoDB. I’m using PHP, but it doesn’t interfere. The problem is in query that I don’t know how to do correctly. I tried to do one that looked like this but it didn’t 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 highest of all sums. This I would like to do without having to give foreach in PHP, so I wanted to do everything in the query now.

Answer 1

To take the maximum amount, regardless of the group:

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

To take the values per group (UF):

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

To take only the highest value of a given group:

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

References:

• Aggregation
• Mongodb - Aggregate and Group example
• mongodb Aggregation php
• Mongodb driver