SOQL Aggregate Functions: COUNT, SUM, AVG, GROUP BY in Apex

Salesforce SOQL supports six aggregate functions: COUNT(), COUNT(fieldName), SUM(), AVG(), MIN(), and MAX(). These work with GROUP BY to group results by a field, and HAVING to filter those groups. In Apex, aggregate queries return List<AggregateResult> instead of List<SObject>.

The Six Aggregate Functions

COUNT() — Simple Row Count

COUNT() without a field name returns the total number of rows. Without GROUP BY it can be used directly as an Integer in Apex:

// Returns Integer directly — no AggregateResult needed
Integer oppCount = [
    SELECT COUNT()
    FROM Opportunity
    WHERE StageName = 'Closed Won'
];
System.debug('Won opps: ' + oppCount);

As soon as you add GROUP BY, you must use COUNT(Id) instead of COUNT(), and the result becomes AggregateResult:

// GROUP BY requires COUNT(Id), returns AggregateResult
List<AggregateResult> results = [
    SELECT StageName, COUNT(Id) cnt
    FROM Opportunity
    GROUP BY StageName
];

for (AggregateResult ar : results) {
    System.debug(ar.get('StageName') + ': ' + ar.get('cnt'));
}

SUM, AVG, MIN, MAX

These functions all follow the same pattern — field name as argument, alias in the query, get(alias) in Apex:

// Revenue summary by Account
List<AggregateResult> rev = [
    SELECT AccountId,
           SUM(Amount) totalRev,
           AVG(Amount) avgDeal,
           MAX(Amount) biggestDeal,
           COUNT(Id) dealCount
    FROM Opportunity
    WHERE StageName = 'Closed Won'
    GROUP BY AccountId
];

for (AggregateResult ar : rev) {
    Id accId       = (Id)     ar.get('AccountId');
    Decimal total  = (Decimal) ar.get('totalRev');
    Decimal avg    = (Decimal) ar.get('avgDeal');
    Decimal max    = (Decimal) ar.get('biggestDeal');
    Integer count  = (Integer) ar.get('dealCount');
    System.debug(accId + ': ' + total + ' (' + count + ' deals)');
}

HAVING — Filtering Grouped Results

HAVING filters groups after aggregation — equivalent to a WHERE on the aggregate result. Use it to find groups that meet a threshold:

// Accounts with more than 5 won opportunities
List<AggregateResult> highValue = [
    SELECT AccountId, COUNT(Id) wonCount
    FROM Opportunity
    WHERE StageName = 'Closed Won'
    GROUP BY AccountId
    HAVING COUNT(Id) > 5
];

Key distinction: WHERE filters rows before grouping. HAVING filters groups after aggregation. You can use both in the same query — WHERE StageName = 'Closed Won' filters which rows go into the aggregation, then HAVING COUNT(Id) > 5 filters which groups appear in the result.

GROUP BY ROLLUP and GROUP BY CUBE

SOQL supports two extended grouping modes:

GROUP BY ROLLUP produces subtotals at each level of the grouping hierarchy. A query grouped by ROLLUP(StageName, AccountId) returns rows for each StageName + AccountId combination, then summary rows for each StageName, then a grand total row. Grouping level is null in summary rows.

// ROLLUP — subtotals per stage + grand total
List<AggregateResult> rollup = [
    SELECT StageName, AccountId,
           SUM(Amount) total
    FROM Opportunity
    GROUP BY ROLLUP(StageName, AccountId)
];

GROUP BY CUBE produces subtotals for all possible combinations of the grouping fields. Use ROLLUP for hierarchical summaries and CUBE for cross-tabular reports.

AggregateResult in Detail

AggregateResult is a read-only map-like object. The get(String aliasOrFieldName) method returns an Object that must be cast to the expected type. Grouped fields are accessed by their API name; aggregate functions are accessed by their alias.

// Multiple groups + correct casting
List<AggregateResult> byOwner = [
    SELECT OwnerId,
           SUM(Amount) pipeline,
           COUNT(Id) oppCount,
           AVG(Probability) avgProb
    FROM Opportunity
    WHERE IsClosed = false
    GROUP BY OwnerId
    ORDER BY SUM(Amount) DESC
    LIMIT 10
];

for (AggregateResult ar : byOwner) {
    Id      ownerId = (Id)      ar.get('OwnerId');
    Decimal pipe    = (Decimal)  ar.get('pipeline');
    Integer cnt     = (Integer)  ar.get('oppCount');
    Decimal prob    = (Decimal)  ar.get('avgProb');
}

Aggregate Queries in Triggers

Aggregate queries are one query against the 100 SOQL limit — but aggregate against potentially millions of rows. They are exempt from the 50,000-row retrieval limit because they return AggregateResult objects, not SObject rows. This makes them ideal for rollup-style computations in triggers.

Classic pattern: a trigger on a child object needs to update a summary field on the parent. Instead of querying all children and summing in Apex, use one aggregate query per batch:

// Trigger: update Account's Total_Revenue__c when Opp closes
trigger OpportunityTrigger on Opportunity (after update) {

    Set<Id> accountIds = new Set<Id>();
    for (Opportunity opp : trigger.new) {
        if (opp.AccountId != null &&
            opp.StageName == 'Closed Won') {
            accountIds.add(opp.AccountId);
        }
    }

    if (accountIds.isEmpty()) return;

    // One aggregate query for all affected accounts
    List<AggregateResult> totals = [
        SELECT AccountId,
               SUM(Amount) totalRev
        FROM Opportunity
        WHERE AccountId IN :accountIds
          AND StageName = 'Closed Won'
        GROUP BY AccountId
    ];

    List<Account> toUpdate = new List<Account>();
    for (AggregateResult ar : totals) {
        Id accId = (Id) ar.get('AccountId');
        Decimal rev = (Decimal) ar.get('totalRev');
        toUpdate.add(new Account(
            Id = accId,
            Total_Revenue__c = rev
        ));
    }
    update toUpdate;
}

This pattern uses one SOQL query and one DML statement regardless of how many opportunities or accounts are in the trigger batch. It is fully bulkified.

Ordering and Limiting Aggregate Results

Aggregate queries support ORDER BY using either the grouped field or the aggregate expression:

// Order by aggregate result descending
List<AggregateResult> topAccounts = [
    SELECT AccountId,
           SUM(Amount) revenue
    FROM Opportunity
    WHERE StageName = 'Closed Won'
    GROUP BY AccountId
    ORDER BY SUM(Amount) DESC NULLS LAST
    LIMIT 20
];

MIN and MAX on Dates

MIN() and MAX() work on Date, DateTime, and String fields as well as numbers. Useful for finding the earliest or most recent activity per group:

// Last activity date per Account
List<AggregateResult> lastActivity = [
    SELECT AccountId,
           MAX(CreatedDate) lastCreated
    FROM Task
    WHERE Status = 'Completed'
    GROUP BY AccountId
];

for (AggregateResult ar : lastActivity) {
    DateTime dt = (DateTime) ar.get('lastCreated');
    System.debug(ar.get('AccountId') + ': ' + dt.date());
}

Common Pitfalls

• Forgetting to cast: ar.get('total') returns Object. Always cast: (Decimal) ar.get('total').
• Using COUNT() in a GROUP BY query: Causes a compile error. Use COUNT(Id) instead.
• No alias, accessing expr0: Fragile — any query reorder breaks the index. Always alias aggregate functions.
• Selecting non-grouped, non-aggregate fields: Any field in SELECT that is not in GROUP BY and is not an aggregate function causes a compile error.
• AVG on null values: Null rows are excluded from AVG calculation. The divisor is the count of non-null rows, not total rows.

For the full picture of SOQL patterns including bulkification, see Session 70: SOQL in For Loops and Bulkification Patterns.