OPERATION_TOO_LARGE: Aggregate query has too many rows for direct assignment, use FOR loop

You wrote a GROUP BY query against Case to count records per owner. It worked in the sandbox for three months. The first time it ran on a Monday morning in production, the org had two thousand and one active owners with at least one Case, and the query threw System.LimitException: OPERATION_TOO_LARGE: Aggregate query has too many rows for direct assignment, use FOR loop. The dashboard tile that depended on it went red. You're now in the platform's most opinionated error message: the one that tells you exactly which keyword you should have used.

What the limit actually is

Apex has two distinct query result caps that look similar but live at different floors.

The plain SOQL query cap is the familiar one: a single Apex transaction can return up to 50,000 rows total across all queries. Hitting it gives you System.LimitException: Too many query rows: 50001.

Aggregate queries (anything with GROUP BY, COUNT(), SUM(), AVG(), MIN(), MAX(), COUNT_DISTINCT()) have a separate, lower cap when you assign them directly to a List<AggregateResult> variable. The exact number has shifted slightly across releases but the practical ceiling is 2,000 distinct aggregate result rows. Past that, you get OPERATION_TOO_LARGE.

The reason the aggregate cap is lower: each AggregateResult row carries more state than a plain SObject row. The platform precomputes the grouping keys, the rollup values, and the aliases. Holding 2,000 such rows in heap is comparable to holding 50,000 plain rows. The cap is calibrated to memory pressure, not to row count alone.

The shape of the broken example

The most common trigger is a GROUP BY over a high-cardinality field with no narrowing WHERE clause:

public class CaseOwnerLeaderboard {
    public static List<AggregateResult> casesPerOwner() {
        return [
            SELECT Owner.Name owner, COUNT(Id) cases
            FROM Case
            GROUP BY Owner.Name
        ];
    }
}

Sandbox has 80 owners. The query returns 80 rows. Tests pass.

Production has 2,500 active users who have been Case owners over the org's lifetime. The query returns 2,500 rows. The runtime refuses to allocate the List<AggregateResult> and throws OPERATION_TOO_LARGE.

The trap is that the surface area of the query (a single SOQL statement) doesn't visibly signal the risk. You see COUNT(Id) and you think "aggregate, that's just one number per group." It is. The problem is the number of groups, not the number of source rows.

The fix is in the message: use a for loop

Apex SOQL for loops stream the result set instead of materializing it. The runtime fetches rows in chunks (200 at a time by default), yields each chunk to the loop body, and discards the chunk before pulling the next one. You never hold all rows at once, so the assignment cap doesn't apply.

public static Map<String, Integer> casesPerOwner() {
    Map<String, Integer> tally = new Map<String, Integer>();
    for (AggregateResult r : [
        SELECT Owner.Name owner, COUNT(Id) cases
        FROM Case
        GROUP BY Owner.Name
    ]) {
        String owner = (String) r.get('owner');
        Integer count = (Integer) r.get('cases');
        tally.put(owner, count);
    }
    return tally;
}

The change is mechanical: instead of List<AggregateResult> rows = [...]; followed by an iteration over rows, you put the SOQL directly in the for header. The query becomes the iterator.

Two practical notes:

Aliases are required. When you call COUNT(Id) or Owner.Name, the result key inside each AggregateResult is expr0, expr1, and so on unless you provide an alias. The cases and owner aliases above make r.get('cases') readable. Without them, you'd write r.get('expr1'), which works but is fragile if you reorder the query.

The for loop counts toward different limits. A SOQL for loop fetches rows in chunks; each chunk counts toward the 50,000 row limit on plain queries, and the grouping cost still counts. You can iterate a large aggregate result without hitting OPERATION_TOO_LARGE, but if downstream you do something that scales with row count (a DML call per group, an HTTP callout per group), you'll hit other governor limits long before you finish.

The fixed example

Walk the broken CaseOwnerLeaderboard to a production-ready shape:

public class CaseOwnerLeaderboard {
    /**
     * Returns owner-name to case-count, streaming via a SOQL for loop.
     * Safe for orgs with thousands of distinct case owners.
     */
    public static Map<String, Integer> casesPerOwner() {
        Map<String, Integer> tally = new Map<String, Integer>();
        for (AggregateResult r : [
            SELECT Owner.Name owner, COUNT(Id) cases
            FROM Case
            WHERE IsClosed = FALSE
              AND CreatedDate = LAST_N_DAYS:90
            GROUP BY Owner.Name
            ORDER BY COUNT(Id) DESC
        ]) {
            tally.put((String) r.get('owner'), (Integer) r.get('cases'));
        }
        return tally;
    }
}

Three changes rolled in: the WHERE clause narrows the data set so most orgs won't even approach the cap, the ORDER BY makes the result useful to the caller, and the result type is a Map instead of a list of opaque AggregateResult objects.

The WHERE clause is the most important improvement. Even with the for-loop streaming, an unbounded GROUP BY is rarely what the business actually wants. Filtering to "open cases in the last 90 days" is closer to a leaderboard intent than "every owner who has ever touched a case." The error pushed you toward a cleaner query.

When the for loop is not enough

Three situations need a heavier tool.

The grouping itself doesn't fit in heap. If you legitimately need 2 million distinct grouping keys and want to do something with each one, even the map you build inside the for loop is too big. The fix is Batch Apex. The batch start() method returns a Database.QueryLocator that the platform chunks for you, and each execute() invocation gets a slice of the data with a fresh governor budget.

You need cross-row analysis. A running total across all groups, a percentile calculation, a comparison between the current row and the previous row. The for loop can do these if you maintain state in a local variable across iterations, but only as long as the accumulated state fits in heap. Above that threshold, Batch Apex is the answer.

The query plan itself times out. For very wide grouping (e.g., grouping by a low-selectivity formula field on a 50-million-row object), the optimizer may not be able to evaluate the query at all. You'll get a QUERY_TIMEOUT instead of OPERATION_TOO_LARGE. The fix here is index design or query rewriting, not Apex code.

Subselects, semi-joins, and the same family

The OPERATION_TOO_LARGE family also fires from:

// Fails: too many rows in the subselect result
List<Account> a = [
    SELECT Id, (SELECT Id FROM Contacts)
    FROM Account
    WHERE Id IN :largeIdSet
];

The parent-child relationship subquery materializes all child rows for each parent. When the total child count exceeds 2,000, you get the same error. The fix patterns are different: bound the inner query with a WHERE clause, or restructure the query to fetch parents and children separately.

Semi-join queries (WHERE Id IN (SELECT ParentId FROM Child)) have a 200,000-row inner cap. Past that, they fail with a different but related error. Same family of failure, same family of fix: bound or paginate.

Testing for this in advance

The reliable way to catch OPERATION_TOO_LARGE before production is a stress test that seeds enough data:

@isTest
static void casesPerOwner_handlesHighOwnerCount() {
    List<User> owners = new List<User>();
    Profile p = [SELECT Id FROM Profile WHERE Name = 'Standard User' LIMIT 1];
    for (Integer i = 0; i < 2500; i++) {
        owners.add(new User(
            ProfileId = p.Id,
            Username = 'stress' + i + '@test.example.com.invalid',
            Alias = 'st' + (i + '').left(5),
            Email = 'stress@test.invalid',
            LastName = 'Tester',
            TimeZoneSidKey = 'America/Los_Angeles',
            LocaleSidKey = 'en_US',
            EmailEncodingKey = 'UTF-8',
            LanguageLocaleKey = 'en_US'
        ));
    }
    insert owners;

    List<Case> cases = new List<Case>();
    for (User u : owners) {
        cases.add(new Case(Status = 'New', OwnerId = u.Id, Subject = 'stress'));
    }
    insert cases;

    Test.startTest();
    Map<String, Integer> tally = CaseOwnerLeaderboard.casesPerOwner();
    Test.stopTest();

    System.assertEquals(2500, tally.size(), 'Should tally all 2,500 owners');
}

The test seeds 2,500 owners and confirms the for-loop fix tolerates the volume. If you run this test against the original (broken) casesPerOwner that returned a List<AggregateResult>, it throws and the test fails. That's the safety net.

A quick comparison to the plain-query family

All four share the same remediation tree: bound the data set with WHERE, stream with a SOQL for loop, or escalate to Batch Apex. The order of escalation runs from cheapest to heaviest. Try WHERE first.

What the platform isn't telling you in the error message

The error names the fix (the for loop), but it doesn't tell you why this particular query crossed the threshold. Two common patterns hide under the surface, and recognizing them shortens the debug.

Cardinality explosion from a join. Adding a child relationship to a GROUP BY query multiplies the row count by the average number of children per parent. A query that grouped 500 accounts by industry was small; the same query joined to Contacts and grouped by (Industry, Contact.Department) can be 50,000 rows. The Setup → Object Manager view of either object shows you the typical row counts. Use it before designing the query, not after.

Latent growth from historical data. A GROUP BY over OwnerId returns one row per distinct owner who has ever touched the object. Even if 90% of those owners left the company two years ago, their records still count toward distinct grouping keys unless you filter on user status. Joining to User and filtering IsActive = TRUE cuts a stale-owner tail off most leaderboards.

Performance properties of the for-loop fix

The for loop streams rows, but the throughput isn't free. Each chunk fetch incurs a small per-batch overhead. For a query that returns 10,000 aggregate rows, the loop will pull ~50 chunks of 200 rows each, with a network round-trip per chunk. The total elapsed time on a synchronous request can exceed your method's CPU budget if the loop body does meaningful work per row.

If you find the for-loop method consistently hits Apex CPU time limit exceeded, that's a signal to push the work into Batch Apex or a Queueable. Batch Apex gives each chunk its own fresh CPU budget; Queueable gives the entire job a fresh budget compared to the calling synchronous context. Either is a better fit than continuing to squeeze the synchronous path.

A trap with HAVING clauses

Adding HAVING to filter aggregate results does not narrow the rows the platform must consider before allocation. HAVING filters happen after the grouping is complete. The GROUP BY step still produces all the candidate rows, and OPERATION_TOO_LARGE fires on the candidate count, not the filtered count.

// Still hits OPERATION_TOO_LARGE if the unfiltered GROUP BY produces too many rows
List<AggregateResult> r = [
    SELECT Owner.Name, COUNT(Id) cases
    FROM Case
    GROUP BY Owner.Name
    HAVING COUNT(Id) > 100
];

The fix: combine HAVING with a WHERE clause that narrows the source rows before grouping. WHERE Status != 'Closed' removes a huge slice of closed cases before the group operation runs, dropping the candidate row count.

Further reading from Salesforce

• Apex Developer Guide: SOQL For Loops
• Apex Developer Guide: Execution Governors and Limits
• SOQL Reference: Using GROUP BY
• Apex Developer Guide: Working with Aggregate Results
• Apex Developer Guide: Using Batch Apex