How to use aggregate function?

Question

I am creating a trigger on Opportunity, whenever new Opportunity is inserted the value of Amount field get summed up for all other Opportunity of the same parent Account and display the sum on the Account object in the custom field Total_Amount__c. I have created

trigger sumOpportunity on Opportunity (after insert) {
    
    list<Opportunity> newOpportunity = trigger.new;
    Set<Id> parentAccId = new Set<Id>();
    
    for(Opportunity opp : newOpportunity){
        parentAccId.add(opp.AccountId);
    }
    
    List<account> parentAcc = [Select Total_Amount__c ,(Select Amount from Opportunities) from Account where Id in :parentAccId];
    
    for(Account acc : parentAcc){
        
        decimal totalAmt = 0;
        
        for(Opportunity opp : acc.Opportunities){
            totalAmt += opp.Amount;
        }
        
        acc.Total_Amount__c = totalAmt;
    }
    
    if(parentAcc.size() > 0){
        update parentAcc;
    }

}

so is there a way that we can use aggregate function to sum the amount on opportunity so that we don't have to use nested loop (first iterating over account then on opportunity).

Answer 1

Hope this will help Trigger :

trigger OpportunityTrigger on Opportunity (after insert) {
    if(Trigger.isAfter && Trigger.isInsert){
        OpportunityTriggerHandler.updateAmountOnInsert(Trigger.new);
    }
}

Apex Class :

public class OpportunityTriggerHandler {
    public static void updateAmountOnInsert(List<Opportunity> newList){
        Set<Id> accIdSet = new Set<Id>();
        List<Account> accList = new List<Account>();
        for(Opportunity opp : newList){
            if(opp.AccountId != null){
                accIdSet.add(opp.AccountId);
            }
        }
        if(!accIdSet.isEmpty()){
            for(AggregateResult agg : [SELECT SUM(Amount) totalsum,AccountId FROM Opportunity WHERE AccountId IN:accIdSet GROUP BY AccountId] ){
                Account a = new Account(Id = (Id)agg.get('AccountId'));
                a.Total_Amount__c = a.Total_Amount__c != null ? a.Total_Amount__c + (Decimal)agg.get('totalsum') : (Decimal)agg.get('totalsum');
                accList.add(a);
            }
            if(!accList.isEmpty()){
                update accList;
            }
        }
    }
}

Answer 2

You can try Aggregate Functions

SELECT AccountId, SUM(Amount) FROM Opportunity WHERE AccountId IN : parentAccId GROUP BY AccountId

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

and

update new Account(Id = AccountId, Total_Amount__c = sum); // this code needs to be updated, just idea

Thanks