How to reference field from an AggregateResult Map?

Question

I am receiving an error when trying to reference the field alias CaseCount__c in my map. It says "Variable does not exist: CaseCount__c". How can I reference this field value?

List<AggregateResult> results = [
    SELECT AccountId Id, COUNT(Id) CaseCount__c
    FROM Case
    WHERE AccountId IN :custList
        AND IsClosed = FALSE
    GROUP BY AccountId
];

//Create a map of Case counts
Map<Id, AggregateResult> caseMap = new Map<Id, AggregateResult>(results);

//Loop over Accounts
for (Account a : custList) {
    if(caseMap.containsKey(a.Id)) {
    System.debug(caseMap.get(a.Id).CaseCount__c);
    } 
} //End Loop

Answer 1

You need to use the get(String fieldName) method (of the SObject class) to access things from an aggregate result.

e.g.

System.debug(caseMap.get(a.Id).get('CaseCount__c'));

Note that get() returns an Object by necessity. If you need to use a piece of data from an AggregateResult, you'll need to do some explicit typecasting.

e.g.

Integer caseCount = (Integer)caseMap.get(a.Id).get('CaseCount__c');

Answer 2

The error you're seeing is due to the fact that the field alias CaseCount__c does not exist on the AggregateResult object. The AggregateResult object is a special object used to hold the results of a SOQL query with aggregate functions (such as COUNT, SUM, MIN, etc.). The field names on this object correspond to the aliases you provide in your query, rather than the actual field names on the objects being queried.

To reference the count of Cases for each account, you will need to use the get method on the AggregateResult object to retrieve the value of the field. Since you aliased the field as CaseCount__c in your query, you will need to use that same alias when retrieving the value.

Here's an example of how you can reference the Case count in your loop:

for (Account a : custList) {
    if(caseMap.containsKey(a.Id)) {
        AggregateResult result = caseMap.get(a.Id);
        Integer caseCount = (Integer) result.get('CaseCount__c');
        System.debug(caseCount);
    } 
}

In this example, we're using the get method to retrieve the value of the CaseCount__c field from the AggregateResult object. We're then casting it to an Integer and using System.debug() to print the value.

You could also use caseMap.get(a.Id).get('CaseCount__c') which will return the count directly without casting.