SOQL SUM() function not calculating the correct value

Question

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?

Answer 1

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.