2

Why does using GROUP BY ROLLUP multiply the number of query rows returned by the number of fields you are rolling up + 1?

Example: I have 3408 records in the database. If I run:

[SELECT Sex__c, IsLow__c, COUNT(Id) FROM MyObject__c GROUP BY Sex__c, IsLow__c]

I get the number of query rows as 3408. If I run:

[SELECT Sex__c, IsLow__c, COUNT(Id) FROM MyObject__c GROUP BY ROLLUP(Sex__c, IsLow__c)]

I get the number of query rows as 10224.

It seems that the ROLLUP is 'touching' each record for each grouping field and then again for a total.
Is this the case? If so, how can I get around it when I have to run the query on larger data sets? Why is this not documented anywhere?

2

Your result is a grand total of subtotals of each combination of your fields. COUNT(id) is matched on each sublevel and counted many times in the rollup.

I can't see clearly on how your data is structured and how you want it to roll up and how it actually does rollup on the sublevels. You are telling us only the grand total result. A simplified example tabel would help. Or a screenshot.

If you're happy with the subtotals and your only concern it the grand total, just query twice. Second time without GROUP, only a plain count(id)

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_groupby_rollup.htm

The GROUP BY ROLLUP clause adds subtotals at different levels, aggregating from right to left through the list of grouping columns. The order of rollup fields is important. A query that includes three rollup fields returns the following rows for totals:

  • First-level subtotals for each combination of fieldName1 and fieldName2. Results are grouped by fieldName3.
  • Second-level subtotals for each value of fieldName1. Results are grouped by fieldName2 and fieldName3.
  • One grand total row
| improve this answer | |

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.