Using Aggregate SOQL queries/results in Batch Apex

I had created a schedulable batch apex that implements Database.Batchable<sObject> in Salesforce, but if you want to use SOQL having aggregate functions like SUM(), MAX(), COUNT() on results grouped by “GROUP BY” clause in start execution, changing to interface Database.Batchable<AggregateResult> isn’t a workable way, because it fails with the below compile error :

Class must implement the global interface method: Iterable<AggregateResult> start(Database.BatchableContext) from Database.Batchable<AggregateResult>

The following sample code will explain this.

/*
 * Compile error : Class must implement the global interface method: Iterable<AggregateResult> start(Database.BatchableContext) from Database.Batchable<AggregateResult>
 */
global class SampleAggregateBatch implements Database.Batchable<AggregateResult> {
  // The batch job starts
  global Database.Querylocator start(Database.BatchableContext bc){
    String query = 'SELECT COUNT(Id), AccountId FROM Contact GROUP BY AccountId';
    return Database.getQuerylocator(query);
  } 
  // The batch job executes and operates on one batch of records
  global void execute(Database.BatchableContext bc, List<sObject> scope){ }
  // The batch job finishes
  global void finish(Database.BatchableContext bc){ }
}

To fix this, using Apex classes that implement Iterator<AggregateResult> and Iterable<AggregateResult> seems the only way for now.

Here is the outline what we should do.
1. Create an Apex class implements Iterator<AggregateResult>.
2. Create an Apex class implements Iterable<AggregateResult>.
3. Implementing to Database.Batchable<AggregateResult>, and Using Iterable at start execution in Batch Apex.

Let’s get started.

Iterator Apex class

Create an Apex class named “AggregateResultIterator” with the following source code :

global class AggregateResultIterator implements Iterator<AggregateResult> {
  AggregateResult [] results {get;set;}
  // tracks which result item is returned
  Integer index {get; set;} 
       
  global AggregateResultIterator(String query) {
    index = 0;
    results = Database.query(query);            
  } 

  global boolean hasNext(){ 
    return results != null && !results.isEmpty() && index < results.size(); 
  }    

  global AggregateResult next(){        
    return results[index++];            
  }       
}    

Iterable Apex class
Create an Apex class named “AggregateResultIterable” with the following source code :

global class AggregateResultIterable implements Iterable<AggregateResult> {
  private String query;

  global AggregateResultIterable(String soql){
    query = soql;
  }

  global Iterator<AggregateResult> Iterator(){
    return new AggregateResultIterator(query);
  }
}

Batch Apex
Then implement Batch Apex from Database.Batchable<AggregateResult>, and use Iterable<AggregateResult>, AggregateResultIterable instead of Database.QueryLocator at start execution, as the following source code :

global class SampleAggregateBatch implements Database.Batchable<AggregateResult> {
  // The batch job starts
  global Iterable<AggregateResult> start(Database.BatchableContext bc){
    String query = 'SELECT COUNT(Id) cnt, AccountId FROM Contact GROUP BY AccountId';
    return new AggregateResultIterable(query);
  } 
  // The batch job executes and operates on one batch of records
  global void execute(Database.BatchableContext bc, List<sObject> scope){ 
    for(sObject sObj : scope) {
      AggregateResult ar = (AggregateResult)sObj;
      System.debug('>>>> COUNT : ' + ar.get('cnt'));
    }
  }
  // The batch job finishes
  global void finish(Database.BatchableContext bc){ }
}

Operation check
Run SampleAggregateBatch and check debug log.

// Run batch apex
SampleAggregateBatch batch = new SampleAggregateBatch();
Database.executebatch(batch, 200);

// Debug log output
11:36:35.0 (14518157)|USER_DEBUG|[11]|DEBUG|>>>> COUNT : 1
11:36:35.0 (14903974)|USER_DEBUG|[11]|DEBUG|>>>> COUNT : 3
11:36:35.0 (15035196)|USER_DEBUG|[11]|DEBUG|>>>> COUNT : 1

Reference Link : 

https://help.salesforce.com/articleView?id=000333422&type=1&mode=1

https://www.xgeek.net/salesforce/using-aggregate-soql-queries-results-in-batch-apex/

It seems working fine! : )

Enjoy it!