SOQL aggregate query limit [Updated for Summer18]

I’m trying to use aggregate queries to save work and script statements. The object has a field for each month representing energy usage for that month for a building.

In situations where a building has no usage history (we don’t have it, or it’s brand new), I want to take the average of all the buildings of that class in that city and use that as a default assumption.

It works great, except for the very large cities where I’m hitting a SOQL limit.

SELECT average(january__c), ... FROM annual_usage__c WHERE ... GROUP BY city__c LIMIT 50000 

Doesn’t seem to limit it in any way…it’ll still come back with more than 50000.

Is there a safe way to deal with the 50000 row limit on aggregate queries? I haven’t even been able to catch this error successfully


I think that the issue here is that an aggregate query is considered to have accessed every record included in the aggregation. Thus the 50000 row limit isn’t the number of records returned by the query, its the number of records ‘touched’. As you have a clause of ‘LIMIT 50000’, you are asking for a maximum of 50000 aggregates to be returned by the query and if any of these “touches” more than one record, you will exceed the limit.

The only exception to this is if your class is annotated @readonly, in which case there are no record limits on queries (although the others still apply, (e.g. heap space). However, this can only be used in scheduled classes or webservices, so may not be useful in your situation.

If you can’t use @readonly, you’ll need to set the value of the LIMIT clause to one which is low enough that all of the records considered in the aggregations is less than 50000. If you have cities that have more than 50000, then there won’t be a low enough limit.

If the relationship between building and city is master detail, you could use a roll up summary field to calculate the averages, although you are limited to 10 of these fields per record.

Source : Link , Question Author : Shane McLaughlin , Answer Author : Bob Buzzard

Leave a Comment