Apex Code Development You need to sign in to do that
Don't have an account?
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);
}
}
I found that if I apply an alias to Account.name and access it with the aggr.get('aliasName') . All works fine!
All Answers
Greetings to you!
When using aggregate functions like count, all other fields must be grouped. Try below query:
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
Issue now, I am trying to print the Account.Name, but I only get the "Name" in place.
Output:
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.'); }I found that if I apply an alias to Account.name and access it with the aggr.get('aliasName') . All works fine!