You need to sign in to do that
Don't have an account?
Issue with Aggregate function
Hi all, I am trying to use the agregate function in the following method. I am attempting to get the average value of invoie totals per the query below. But it appears to only reflect one record from the query. For example, if my invoice amounts are $5, $10 and $12. I am getting returned one of the values, but not the average. Can someone tell me what I am doing wrong.
Thanks
global void execute(Database.BatchableContext BC, List<sObject> scope){
Map<Id, Decimal> janavg = new Map<Id, Decimal>();
List<Account> accts2updateJan = new List<Account>();
for(sObject s : scope)
{Account a = (Account)s;
janavg.put(a.Id,0);
}
AggregateResult[] jangr = [SELECT Account__c aid, PostPeriod__c, AVG(InvoiceTotal2__c) AvgInvoice From ESDInvoice__c e WHERE Account__c IN :janavg.keyset() AND CALENDAR_MONTH(PostPeriod__c) = 1 GROUP BY Account__c, PostPeriod__c];
//get jan
for (AggregateResult ar : jangr) {
if (janavg.containsKey((Id)ar.get('aid'))) {
janavg.put((Id)ar.get('aid'), (Decimal)ar.get('AvgInvoice'));
System.debug('Status -- Jan Average From query = ' + (Decimal)ar.get('AvgInvoice'));
}
}
Issue was in the Group By clause. Removed the second val(postperiod) and the average correctly.