System.LimitException: Too many query rows: 50001 but only have 32000 rows

Question

I have an aggregate function in the below SOQL query, this query is executed with this method Database.countQuery(), I only have 32000 rows but the error says I am getting 50001 rows.

SELECT COUNT() 
FROM Opportunity 
WHERE Account.BillingState = 'NY'

update

I also use this query to get the SUM and to get the numbers of rows:

SELECT SUM(Amount)suma, Count(Id)cantidad 
FROM Opportunity

Answer 1

The problem is that your first query returns 32,000 rows and your second query is actually including over 32,000 rows in the aggregation.

The first query is limiting the Opportunity results by the Account.BillingState which is returning the 32,000 rows. The second query is including ALL Opportunities since there is no WHERE clause. Even if you included the same WHERE clause as the first query, it would still be over the limit of 50,000 (total would be 64,000 rows).

You need to make sure you are being more selective in your queries and filtering to just the records you need. If you cannot make it more selective, consider asynchronous apex.

See documentation here: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

Queries that include aggregate functions are subject to the same governor limits as other SOQL queries for the total number of records returned. This limit includes any records included in the aggregation, not just the number of rows returned by the query. If you encounter this limit, you should add a condition to the WHERE clause to reduce the amount of records processed by the query.