[SalesForce] GROUP BY / ROLLUP Query Rows

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?

Best Answer

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

Related Solutions

[SalesForce] getting group by aggregate errors in this query

The rule of thumb here is

Any field thats included in Aggregate SOQL has to be either aggregated or grouped

When using aggregate functions like count, all other fields must be grouped.

The final query will be

Select BOG_Membership_Type_New__c, Contact_Name__c,COUNT(ORG__C)
 FROM BOG_Membership__c 
 WHERE BOG_Membership_Status__c = 'Active' 
 AND BOG_Membership_Type_New__c <> ''
 GROUP BY BOG_Membership_Type_New__c, Contact_Name__c
 ORDER BY BOG_Membership_Type_New__c , Contact_Name__c

[SalesForce] Displaying month/year from createdDate in SOQL query

You can do 2 things:

1) Create new field and update it with workflow. Then you can use field in your query

TEXT(YEAR(DATEVALUE(CreatedDate)))  & ' ' &
CASE(MONTH(DATEVALUE(CreatedDate)), 1, "January", 2, "February", 3, "March",  4, "April",  5, "May",  6, "June", 7, "July", 8, "August", 9, 
"September", 10, "October", 11, "November", 12, "December", "None")

2) Write query like this and merge month and year in conga

SELECT CALENDAR_year(CreatedDate) Year, CALENDAR_MONTH(CreatedDate) month, 
COUNT(CreatedDate) leadcount 
FROM Lead 
WHERE CreatedDate>=LAST_YEAR 
AND CreatedDate<THIS_MONTH 
AND LeadSource not in ('MailChimp') 
GROUP BY CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate) 
ORDER BY 
CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate)

You can use if statement in conga to convert 1 to Jan and so on.

{IF “Expression1” Operator “Expression2” “TrueText” “FalseText” }

Best Answer

Related Solutions

[SalesForce] getting group by aggregate errors in this query

[SalesForce] Displaying month/year from createdDate in SOQL query

Related Topic