Local Grouping
Salesforce has many limitations when it comes to SOQL GROUP BY feature.
- You cannot group by if matched records produce more than 2000 groups. If so you will get error
EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch - You cannot group by certain field types (For ex., Formula Fields). See this Post for a full list of non-groupable field types.
Brobench allows you to work around these issues by fetching all data and do the group by locally. Brobench Local Group by has no limitations of field types, number of records (upto to your system memory limit)
Group By Fields
- Run the SOQL to match all records that you want to use for grouping
- Fetch all records from the server into Datagrid (you can use Fetch More -> All Batches to fetch all records)
- Right-click on the field you want to group by and select
Group -> Add to Groups - App will group the records by the selected field.
- If you want to group by additional fields, follow the same steps and the app will further group by new fields.
Change Aggregate Function
By default, it applies distinct function to Non-Grouped Fields. But you can change that by right-clicking on the field and selecting
Group -> Set Aggregate Function -> <Function>.
Below are the available aggregate functions.
| Function | Description |
|---|---|
Count | Number of records in the group which have non-blank for that field |
Distinct | Number of distinct non-blank values for that field in that group |
Sum | Applicable for Number fields only. Sum of all the values in the group. Nulls are treated as 0 |
Min | Applicable for Number fields only. Minimum value of all the values in the group. Nulls are treated as 0 |
Max | Applicable for Number fields only. Maximum value of all the values in the group. Nulls are treated as 0 |
Avg | Applicable for Number fields only. Average of all the values in the group. Nulls are treated as 0 |
List | Returns the distinct list of values in the group as comma separated list. |
List with Count | Returns the distinct list of values in the group as comma separated list along with the occurence count. |
First | Returns the first value in the group |
Last | Returns the last value in the group |
Export Group Summary
If you do standard Datagrid Export it will export the source records and not the aggregated summary. To export
the summary, right-click in the datagrid and select Export Other -> Export Group Summary as CSV or Export Group Summary as Excel.
Examples
Group by Account Billing City
We have this soql select BillingCity, count(id) from Account group by BillingCity which throws error
EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch
As a workaround, let's run query select Id, BillingCity, Name from Account (make sure you select all fields you want to group by and create
aggregate summary of) and fetch all records into Datagrid.
Right-click on Billing City field and select Group -> Add to Groups
App shows the summary of the grouped records.
