+ Start a Discussion
Timothy SmithTimothy Smith 

How to Access Account.Name field within SOQL using Aggregate

I would like to send an email anytime an account has created 8 cases in the last 8 days.  The email is sending, but within the email, the AccountId (id) is showing.  How would I get the much more readable Account.Name field to show.   
Line: 1, Column: 44
Field must be grouped or aggregated: Name
 
List<AggregateResult> AggregateResultList =[SELECT AccountId, Account.name, COUNT(Id) co
					    FROM Case
					    WHERE CreatedDate = LAST_N_DAYS:8
					    GROUP BY AccountId
					    HAVING COUNT(Id) > 1];

	for(AggregateResult aggr:AggregateResultList){ 
   		 
      		  //Send Email to Implementation Coordinator
  		  Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
   		 message.toAddresses = new String[] { 'test@test.com' };   
   		 message.subject = 'Subject Test Message';
   		 message.plainTextBody = 'Account name: ' + (id)aggr.get('AccountId') + ' has ' + (Integer)aggr.get('co') + ' cases opened in the last 8 days.';
   		 Messaging.SingleEmailMessage[] messages =   new List<Messaging.SingleEmailMessage> {message};
   		 Messaging.SendEmailResult[] results = Messaging.sendEmail(messages);
    
  		  if (results[0].success) {
    		    System.debug('The email was sent successfully.');
   		 } else {
    		    System.debug('The email failed to send: ' + results[0].errors[0].message);
   		 }     
    	     
    }

 
Best Answer chosen by Timothy Smith
Timothy SmithTimothy Smith
Solved:

I found that if I apply an alias to Account.name and access it with the aggr.get('aliasName') . All works fine!

All Answers

Khan AnasKhan Anas (Salesforce Developers) 
Hi Timothy,

Greetings to you!

When using aggregate functions like count, all other fields must be grouped. Try below query:
SELECT AccountId, Account.name, COUNT(Id) co
					    FROM Case
					    WHERE CreatedDate = LAST_N_DAYS:8
					    GROUP BY AccountId, Account.Name
					    HAVING COUNT(Id) > 1

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

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future. It will help to keep this community clean.

Thanks and Regards,
Khan Anas
Timothy SmithTimothy Smith
@Khan Anas, thank you, I am now one step closer.  I am now able to conduct the SOQL search.

Issue now, I am trying to print the Account.Name, but I only get the "Name" in place.

Output:
User-added image

Code:  
 
List<AggregateResult> AggregateResultList = [SELECT AccountId, Account.name,COUNT(Id) co
					                         FROM Case
					                         WHERE CreatedDate = LAST_N_DAYS:5
					                         GROUP BY AccountId, Account.Name
					                         HAVING COUNT(Id) >=8];

	for(AggregateResult aggr:AggregateResultList){ 
    
  		  System.debug('Account name: ' + Account.Name + ' has ' + (Integer)aggr.get('co') + ' cases opened in the last 8 days.');
        
    }

 
Timothy SmithTimothy Smith
Solved:

I found that if I apply an alias to Account.name and access it with the aggr.get('aliasName') . All works fine!
This was selected as the best answer