5

I have a SOQL query that I'm trying to use the SUM function with. My issue is that the SUM function gives me a totally different result than if i just query the rows and add up the amounts

Query 1 - Selecting the rows individually

select id,  Parts_Cost__c from customObject where Status= 'Completed' 
and Contract = :contractId and Completed_On__c >= :contractStartDate  
and Completed_On__c <= :contractEndDate

The individual results for parts cost sum to- 6773.59 and 17 rows are returned

Query 2- Aggregate Results:

select count(id),  sum(Parts_Cost__c) from customObject where Status= 'Completed'
and Contract = :contractId and Completed_On__c >= :contractStartDate  
and Completed_On__c <= :contractEndDate

This will give me a summed value of 8036.05 and the count result is 17 rows.

Can anyone think of reasons for these discrepancies in the results? Can I see how the 8036.05 is getting calculated?

  • where do you run these SOQLs? – Oleksandr Berehovskyi Nov 27 '17 at 12:30
  • They are running in an Apex Class that I have created, but i've also run them in the workbench to verify the results and am getting the same issue there – denisoleary Nov 27 '17 at 12:32
  • You may be missing something, – NITHESH K Nov 27 '17 at 12:39
  • Just soql only select Parts_Cost__c from customObject where ***.. & check the reuslt . – NITHESH K Nov 27 '17 at 12:40
  • Just to be sure, the variables that you're using to filter your query are the same in both cases, correct? (e.g. contractId, contractStartDate, and contractEndDate). That would be the easiest explanation for the difference. When using aggregate functions, we lose the ability to see the individual records that were pulled by the query, so for purposes of testing it would make sense to hardcode values into your 3 variables there (alternatively, run both queries in the same transaction). – Derek F Nov 27 '17 at 13:45
5

See Querying Currency Fields in Multi-Currency Orgs. Any time you use an aggregate function, it is always in the organization's currency.

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, if you want the values in the user's currency, you can use the convertCurrency function, but you have to do the math yourself:

select id, convertCurrency(Parts_Cost__c) from customObject where Status= 'Completed' 
and Contract = :contractId and Completed_On__c >= :contractStartDate  
and Completed_On__c <= :contractEndDate

...

Decimal total = 0;
for(customObject record: queryResults) {
    total += record.Parts_Cost__c;
}

You can't choose to get values in currency values other than the organization's currency or the user's currency.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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