counting total active contacts for the account

Question

I have created a checkbox on contacts to represent it's status (active__c ) and another custom field for account object, which represent total number of active contacts (activeContact__c).

Below code is working fine but when I am making the total active contacts to zero for any account, it still come as 1.

Could you please explain what I am doing wrong here? Thanks.

trigger CountActiveContacts on Contact (after insert , after update) {
     switch on Trigger.operationType {
        when AFTER_INSERT, AFTER_UPDATE {
            List<Account> accountList = new List<Account>();
            Set<Id> accIds = new Set<Id>();
 
            
            for(Contact c : Trigger.new){                
                    accIds.add(c.AccountId);
            }
            
            AggregateResult[] groupedResult = [select accountid accID, count(id) totalCountOfActiveContacts from contact where active__c = true and AccountId =: accIds group by accountid ];
            
            for(AggregateResult result: groupedResult){
                accountList.add(new Account(id= (id)result.get('accID'), activeContact__c= (Integer) (result.get('totalCountOfActiveContacts'))));
            }
            update accountList;
        }        
     }
}

Answer 1

When last contact got unticked your query starts to return 0 rows

select accountid accID, count(id) totalCountOfActiveContacts 
from contact 
where active__c = true and AccountId =: accIds 
group by accountid

so there's nothing to loop over anymore.

Try making the Accounts as a Map and initialise the counter to zero. And you won't need the Set even

Map<Id, Account> accs = new Map<Id, Account>();
for(Contact c : trigger.new){
    accs.put(c.AccountId, new Account(Id = c.AccountId, ActiveContact__c = 0));
}

AggregateResult[] groupedResult = [select accountid accID, count(id) totalCountOfActiveContacts from contact where active__c = true and AccountId =: accs.keyset() group by accountid ];
            
for(AggregateResult result: groupedResult){
    accs.get((Id) result.get('accID')).ActiveContact__c = (Integer) result.get('totalCountOfActiveContacts');
}