How to handle custom rollup with over 50,000 child records

Question

I have a requirement to rollup the Average, Max, Min, total values on many Child records (over 50,000) to fields on the parent object. I am running into an issue where my Aggregate results query is hitting the too many Query rows error. Does anyone have a solution for how to code this in a way where I can avoid the limit.

Here is the query that is hitting the error, The rest of the code just takes those values and applies them to the parent field assuming the value has changed.

  private AggregateResult[] aggregatePaymentAmounts(Set<Id> GAUIds) {
    return [SELECT 
             npsp__General_Accounting_Unit__c GAU
            ,SUM(npsp__Amount__c) Total, MIN(npsp__Amount__c) Min, MAX(npsp__Amount__c) Max, Avg(npsp__Amount__c) average, COUNT(id) Num
        FROM 
            npsp__Allocation__c
        WHERE
            npsp__Payment__c != null 
            AND 
            (npsp__Payment__r.npe01__Opportunity__r.Type != 'Transfer' AND npsp__Payment__r.npe01__Opportunity__r.Type != 'Third Party Payor')
            AND
            (npsp__Payment__r.npe01__Opportunity__r.StageName = 'Revocable Planned Gift' OR npsp__Payment__r.npe01__Opportunity__r.StageName = 'Pledged' OR npsp__Payment__r.npe01__Opportunity__r.StageName = 'Received' OR npsp__Payment__r.npe01__Opportunity__r.StageName = 'Awarded')
            AND
            (npsp__Payment__r.npe01__Opportunity__r.RecordType.Name = 'Gift' OR npsp__Payment__r.npe01__Opportunity__r.RecordType.Name = 'Grant' OR npsp__Payment__r.npe01__Opportunity__r.RecordType.Name = 'Matching Gift')
            AND 
            npsp__General_Accounting_Unit__c IN :GAUIds
        GROUP BY 
            npsp__General_Accounting_Unit__c];
    

}

Answer 1

You likely do not need to write a line of Apex to achieve this goal (see below). That's good, because the real fix for your issue here is writing a moderately complex Batch Apex class to iterate over child records and hand-compute all of your rollup values. There's plenty of prior art on that, but it's easy to get wrong.

But, speaking of prior art, NPSP has an out-of-the-box feature called Customizable Rollups that appears to me to meet your needs. You can define a Customizable Rollup from the GAU Allocation to the GAU, and apply a Filter Group to limit the rollup by filters on the Opportunity (Donation).

NPSP's Customizable Rollups are implemented by highly optimized and sophisticated Apex batch classes that you don't have to write yourself, and are effective even at data volume much higher than you mention here. You can aggregate for sum, average, total count, largest, and smallest values.

In fact, NPSP's out-of-the-box Customizable Rollups include several that are very close to your goals here; you may be able to clone those rollups and add your custom filters to get what you need.

---

Disclosure: I am a Salesforce employee and previously worked closely with the NPSP team.