+ Start a Discussion

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.




    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;
        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'));



Best Answer chosen by Admin (Salesforce Developers) 

Issue was in the Group By clause. Removed the second val(postperiod) and the average correctly.