11

Salesforce documentation suggests that aggregrate soql results must first be assigned to an instance of the AggregateResult class, as shown below:

    AggregateResult[] groupedResults = [SELECT AVG(Amount)aver FROM Opportunity]; 
    Object avgAmount = groupedResults[0].get('aver');

I'm wondering, is it possible for a more elegant solution that assigns the aggregated field directly to a variable of the same type? EG:

mainAccount.Active_Total_Opps_Rollup__c = [SELECT SUM(Amount) FROM Opportunity WHERE AccountID IN :ID_Group];

Obviously what I've written gives an error, because the query returns a list of results and not a single value. But is there a way for me to compactly indicate that I want to return only the aggregated value?

15

You cannot simplify the query, but you can the assignment...

Decimal avgAmount = (Decimal) [SELECT AVG(Amount)aver FROM Opportunity][0].get('aver');

Normally I would have said this was a little unsafe, since your always assuming you have one item in the result array the aggregate query returns. However I've just tested it and even if the object has no records in it you do get an a single aggregate result. However the value of 'aver' is null.

  • 1
    Thank you for this - makes sense that a list index (the [0]) is needed. Also good to know it returns a null value when nothing is found to aggregate. – smohyee Oct 24 '13 at 15:06
1

One approach can be serialized version of Aggregate Queries:

String JSONString = JSON.serialize(Database.Query('SELECT AVG(Amount)aver FROM Opportunity'));

Refer "Serialization of aggregate query result fields" in APEX Documentation for more information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.