Utilizing the AggregateResult and Map aggregate functions

The AggregateResult and Map classes in Salesforce are primarily used in SOQL queries and Apex code to handle results in more complex queries, especially when using aggregate functions (like SUM(), COUNT(), AVG(), etc.).

Let’s break down how they are typically utilized:

AggregateResult

The AggregateResult class holds the results of a SOQL query that uses aggregate functions. It allows the retrieval of grouped and aggregated data.

Aggregate functions are used in SOQL queries to perform calculations on data, such as SUM(), COUNT(), or AVG(). These functions don’t return individual records but aggregated values.

Example 1: Using `AggregateResult` to Count Opportunities by Stage

// Query to count the number of opportunities grouped by stage
List<AggregateResult> groupedResults = [
    SELECT StageName, COUNT(Id) totalCount 
    FROM Opportunity 
    GROUP BY StageName
];

// Iterate over results
for (AggregateResult ar : groupedResults) {
    // Fetch StageName and totalCount from the result
    String stageName = (String) ar.get('StageName');
    Integer totalCount = (Integer) ar.get('totalCount');
    System.debug('Stage: ' + stageName + ', Total: ' + totalCount);
}

In this query:

The AggregateResult class stores the results of the query.
ar.get('StageName') retrieves the StageName value from the result.
ar.get('totalCount') retrieves the count of records in that stage.

Example 2: Summing Opportunity Amounts by Stage

// Query to sum opportunity amounts grouped by stage
List<AggregateResult> results = [
    SELECT StageName, SUM(Amount) totalAmount 
    FROM Opportunity 
    GROUP BY StageName
];

for (AggregateResult ar : results) {
    String stage = (String) ar.get('StageName');
    Decimal totalAmount = (Decimal) ar.get('totalAmount');
    System.debug('Stage: ' + stage + ', Total Amount: ' + totalAmount);
}

In this case:

The SUM(Amount) is used to calculate the total amount for each StageName.
totalAmount is extracted using ar.get('totalAmount').

Map Aggregate Function

In Apex, a Map is used to store key-value pairs where each key is unique. After performing aggregate queries, developers often store the results Map for easy lookup.

Example: Using a `Map` to Store Results from Aggregate Query

We can combine AggregateResult and Map to make it easier to access the results based on a key (like StageName).


// Query to sum opportunity amounts by stage
List<AggregateResult> results = [
    SELECT StageName, SUM(Amount) totalAmount 
    FROM Opportunity 
    GROUP BY StageName
];

// Create a Map to store stage and corresponding total amount
Map<String, Decimal> stageAmountMap = new Map<String, Decimal>();

// Iterate through the AggregateResult and populate the map
for (AggregateResult ar : results) {
    String stage = (String) ar.get('StageName');
    Decimal totalAmount = (Decimal) ar.get('totalAmount');
    stageAmountMap.put(stage, totalAmount);
}

// Now, we can easily access the total amount by StageName from the map
Decimal newBizAmount = stageAmountMap.get('Closed Won');
System.debug('Total amount for Closed Won: ' + newBizAmount);

Key Takeaways:

AggregateResult: A class that stores the results of SOQL queries that use aggregate functions like COUNT(), SUM(), etc. We access the data in the AggregateResult object using the get() method.
Map: A collection type in Apex that stores data in key-value pairs. It helps store and quickly access aggregated data based on a specific key (e.g., StageName).

This combination of AggregateResult and Map provides flexibility when working with aggregated data, making it easy to process and look up specific results efficiently.

SalesForce Simplified