0

I have this aggregated query which seems to return only 1050 rows but still I am getting a limit exception. This is the APEX query:

AggregateResult[] groupedResults = [SELECT COUNT_DISTINCT(ID) cnt, FISCAL_YEAR(CreatedDate) year, FISCAL_MONTH(CreatedDate) month, Country country 
FROM Lead 
WHERE Should_Include_in_Marketing_Performance__c = true 
GROUP BY ROLLUP(Country, FISCAL_YEAR(CreatedDate), FISCAL_MONTH(CreatedDate))]; 

This is the actual log:

08:32:42:006 SOQL_EXECUTE_BEGIN [66]|Aggregations:0|SELECT COUNT_DISTINCT(ID) cnt, FISCAL_YEAR(CreatedDate) year, FISCAL_MONTH(CreatedDate) month, Country country FROM Lead WHERE Should_Include_in_Marketing_Performance__c = TRUE GROUP BY ROLLUP(Country, FISCAL_YEAR(CreatedDate), FISCAL_MONTH(CreatedDate))

08:32:46:228 SOQL_EXECUTE_END [66]|Rows:1050

08:32:46:229 EXCEPTION_THROWN [66]|System.LimitException: Too many query rows: 50001

Any ideas why I am hitting the limit?

5

The number of rows aggregated count against the 50,000 record limit. The "Rows" value you're seeing are the number of aggregate rows that were returned; this has a limit of 2,000 rows per query; if you exceed that, you'll get a different sort of error. Without knowing more, it's hard to tell what you'll need to do, but it may involve writing a batch class, using the @ReadOnly annotation, or filtering the data differently. This is mentioned in the documentation:

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. (Emphasis added)

| improve this answer | |
  • @sdfcfox I am trying to do a GROUP BY ROLLUP query on Leads with some WHERE condition. I tried doing the same in a batch with size 50K but I am getting this error now: "Aggregate query does not support queryMore()". I cannot find a way to do this aggregated query on many leads? – Dedo Mar 24 '18 at 6:03
  • @Dedo Correct. Aggregate results are limited to 2,000 results. You may need to do the aggregation in your batch class manually by using Database.Stateful. – sfdcfox Mar 24 '18 at 6:05
  • @sdfcfox this is frustrating, I know I will get less than 2000 results but the dataset I am aggregating is larger than 50K – Dedo Mar 24 '18 at 8:16
  • @sfdcfox is there a solution to this? – Matthew Metros Oct 10 '19 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.