2

I'm trying to query a custom object to get the sum of a currency field into a integer value so I can pass it to a visualforce page gauge component. I'm stuck on the query. I can get the values in a list but I cannot seem to sum the field.

AggregateResult[] groupedResults =
[select sum(convertCurrency(amount__c)) sum FROM Actuals__c Where amount__c != null AND ISO_Goal__r.OwnerId = :UserInfo.getUserId()];    
Integer sumAmount = groupedResults[0].get('sum');

I keep getting Illegal assignment from List Actuals__c to List AggregateResult no matter how I do it.

What am I doing wrong?

3 Answers 3

3

The problem is described in the Querying Currency Fields in Multi-currency Orgs documentation:

You can’t convert the result of an aggregate function into the user’s currency by calling the convertCurrency() function. If a query includes a GROUP BY or HAVING clause, currency data returned by using an aggregate function, such as SUM() or MAX(), is in the org’s default currency.

So the assignment will work if you remove the convertCurrency function from it.

You can then do your own conversion by querying the CurrencyType or DatedConversionRate objects for the rates.

Also the summed value will be a Decimal:

Decimal sumAmount = (Decimal) groupedResults[0].get('sum');

but if you really want an integer:

Integer sumAmountAsInteger = sumAmount.intValue();
1
  • this was helpful, the convertCurrency was stopping it. Commented May 2, 2016 at 15:52
0
  List<AggregateResult> groupedResults = [select sum(convertCurrency(amount__c)) FROM Actuals__c Where amount__c != null AND ISO_Goal__r.OwnerId = :UserInfo.getUserId() ];
             if(groupedResults.size()>0 ){ 
                 if(Integer.valueOf(groupedResults[0].get('expr0'))!=null){
                     Integer sumAmount=Integer.valueOf(groupedResults[0].get('expr0'));
                 }
            }

try this one. hope it will help you.

2
  • 3
    This answer would be more helpful if you explained the difference between the code you provided, and the code that the OP (original poster) currently has. The key thing here is that the expression groupedResults[0].get('expr0') returns a result of the generic Object type. To store that result in an Integer variable, we need to do some type casting. This can be done like Integer sumAmount = (Integer)groupedResults[0].get('expr0'), or like Integer sumAmount = Integer.valueOf(groupedResults[0].get('expr0'))
    – Derek F
    Commented May 2, 2016 at 15:15
  • yes type cast needed and i did that too.. : Integer.valueOf(groupedResults[0].get('expr0')). this is how i do type cast in apex. and its working for me. used to get total of the amount and convert to integer and i used the same what i send u (different query). Commented May 3, 2016 at 5:46
0

You are getting mentioned error because you can not assign AggregateResult into integer variable.

  • Please try doing following thing:

    for(AggregateResult objTEMP : [SELECT sum(amount__c) FROM Actuals__c WHERE amount__c != null AND   ISO_Goal__r.OwnerId = :UserInfo.getUserId()])
    {  
      SumInInteger= (Integer)objTEMP.get('expr0');
    }
    

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .