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
WHERE January__c != null AND City__c = :city
?) or consider preparing data upfront... Some batch class that would aggregate these results in a helper object. Or even often overlooked - Analytic Snapshot?