function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Synthia BeauvaisSynthia Beauvais 

System Query Exception: Aggregate Query

I am getting the following error for my contact count trigger below. I am not sure what this error means. It only affects 2 out of 300,000+ accounts in my org. 

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger NumberOfContacts caused an unexpected exception, contact your administrator: NumberOfContacts: execution of BeforeUpdate caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop: External entry point

  
// On Account

trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else
        for(account a:[select id,(select id from contacts) from account where id in :trigger.new])
            trigger.newmap.get(a.id).Number_of_contacts__c = a.contacts.size();
            
        for(account b:[select id,(select id from contacts where Inactive__c = False) from account where id in :trigger.new])
            trigger.newmap.get(b.id).Number_of_active_contacts__c = b.contacts.size();
}



 
Lalit Mistry 21Lalit Mistry 21
Hi Synthia,
Below piece of code should help you. 
trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) as conCount, AccountId as accId FROM Contact WHERE AccountId IN :trigger.new];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
		}

		agResult = [SELECT Count(ID) as conCount, AccountId as accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
		}
	}
}
Synthia BeauvaisSynthia Beauvais
Hi Lalit! 

I am getting Error: Compile Error: unexpected token: as at line 6 column 59 with your code. 
Lalit Mistry 21Lalit Mistry 21
My bad,
Below should work 
trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
		}

		agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
		}
	}
}

 
Synthia BeauvaisSynthia Beauvais
The new trigger is not recalcualting the fields when a contact is marked inactive. Do you know why that is? 
Lalit Mistry 21Lalit Mistry 21
Didn't think through this scneario. Use the below updated trigger 
trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
		}
		
		for(Account act : Trigger.new){
			act.Number_of_active_contacts__c = 0;
		}
		agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
		for(AggregateResult result : agResult){
			trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
		}
	}
}

 
Synthia BeauvaisSynthia Beauvais
Thank you Lalit!!!
Synthia BeauvaisSynthia Beauvais
One more issue with the code. When a contact is deleted, The "Yes" "No" is not being triggered. Can you tell me why that is? 

  
trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];
        for(AggregateResult result : agResult){
            trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
        }
        
        for(Account act : Trigger.new){
            act.Number_of_active_contacts__c = 0;
        }
        agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
        for(AggregateResult result : agResult){
            trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
        }
    }
}

User-added image
Synthia BeauvaisSynthia Beauvais
No worries. Below code resolved the issue.
  
trigger NumberOfContacts on Account (before insert, before update) {
    system.debug('hi');
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];

        for (account a: trigger.new)
            a.Number_of_contacts__c = 0;
        
        for(AggregateResult result : agResult){
            system.debug('num of contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
        }
    
        for(Account act : Trigger.new){
            act.Number_of_active_contacts__c = 0;
        }
        agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
        for(AggregateResult result : agResult){
            system.debug('num of active contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
        }
    }
}

Scenarios:
  1. Delete contact
  2. Undelete contact
  3. Mark contact as inactive
  4. Transfer contact to another account
  5. Lead Converted to Contact