What if you want to create a Batch Apex Job that uses SOQL having aggregate functions like SUM, MAX, and COUNT on top of results grouped by the “GROUP BY” clause? You can’t easily create a Batch job via QueryLocator, as shown below. When working with Batch Apex in Salesforce, you might encounter a scenario where you need to run SOQL queries with aggregate functions like SUM, MAX, or COUNT while grouping results using the GROUP BY clause. A common challenge here is that Batch Apex doesn’t directly support using aggregate functions through the QueryLocator approach, as shown in the following example:

It fails with this error:

Error: Compile Error: QueryLocatorAggregator: Class must implement the global interface method: Iterable<SOBJECT:AggregateResult> start(Database.BatchableContext) from Database.Batchable<SOBJECT:AggregateResult> at line 1 column 14

So the other option that seems doable is using SObject as a generic type argument in Batchable, as shown below with complete source code.

The above code/class complies (saves), but on first processing of batch it fails with this error:

System.UnexpectedException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch

So, how to work on queries with aggregation? Well, the hint was right there in the first error message in red, i.e. use Iterable<AggregateResult>.

Making AggregateResult queries work in Batch Apex

The only way I found is using Iterable<??>. Iterable is an easy QueryLocator alternate to feed data to batch apex execution. Database.Batchable<?> interface gives two abstract methods to implement:

1. global (Database.QueryLocator ) start(Database.BatchableContext bc) {}

2. global (Iterable<sObject>) start(Database.BatchableContext bc) {}

The magical part about Database.Batchable interface is you need to implement either of the above two abstract methods. Though that magic confuses me a lot, because of my Java background 😀.

Here are three steps to get rolling with Iterable<> in Batch Apex:

1. Create a class that implements contract of Iterator<AggregateResult>

2. Create a class that implements contract of Iterable<AggregateResult>

3. Use the instance of Iterable<AggregateResult> implementation in Database.Batchable’s “global (Iterable<sObject>) start(Database.BatchableContext bc) {}”

Here is the code sample for this:

Here is the code sample for that:

Use the instance of Iterable<AggregateResult> implementation in Database.Batchable’s “global (Iterable<sObject>) start(Database.BatchableContext bc) {}”

Here is the complete Database.Batchable class implementation:

When not to use Iterable<?> with AggregateResult

Database.Batchable implementations depending on Iterable<?> for data source are bound to normal governor limits, so Iterable’s can never process 50 million records like QueryLocator. Before using Iterable, you need to watch the amount of Data/Rows resulting from that Aggregated SOQL.

References

• Code Snippet: Full Batch class with all Iterable/Iterator implementations.

• Using Batch Apex

• How to write custom iterator/iterable

• Working with SOQL Aggregate functions

• QueryLocator