Local GROUP BY

Professional

Local GROUP BY is a powerful feature that performs grouping and aggregation on your local machine instead of on Salesforce servers. This overcomes several Salesforce GROUP BY limitations.

Overview

When you use GROUP LOCAL BY in your SOQL query, Brobench:

1. Fetches all matching records from Salesforce (with WHERE filter applied)
2. Performs grouping and aggregation locally
3. Applies HAVING and ORDER BY locally
4. Returns results in the same format as standard SOQL GROUP BY

Syntax

Syntax is same as standard SOQL GROUP BY but add LOCAL keyword. For example.,

SELECT field1, field2, aggregate_function(field3), ...
FROM SObject
WHERE conditions
GROUP LOCAL BY field1, field2, ...
HAVING aggregate_conditions
ORDER BY field1 [ASC|DESC] [NULLS FIRST|LAST]

Key difference: Use GROUP LOCAL BY instead of GROUP BY

Use Local GROUP BY When:

Grouping by unsupported fields

• Text fields longer than 255 characters
• Rich text area fields
• Long text area fields
• Multi-select picklists
• Some geolocation fields

Exceeding the 2000-row limit

• Standard SOQL GROUP BY returns maximum 2000 rows
• Local GROUP BY has no row limit (fetches all matching records)

Working around Salesforce GROUP BY quirks

• Complex aggregation requirements
• Need more flexibility than server-side allows

Don't Use Local GROUP BY When:

❌ Dataset is very large (millions of records)

• All records must be fetched and processed locally
• May cause memory/performance issues
• May cosume too many API calls

❌ Standard GROUP BY works fine

• Server-side GROUP BY is faster
• Use Local GROUP BY only when needed

❌ Need ROLLUP or CUBE

• These are not supported in Local GROUP BY
• Use standard SOQL GROUP BY instead

Basic Examples

Simple Grouping

-- Count accounts by industry
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry

Multiple Aggregates

-- Revenue statistics by industry
SELECT
  Industry,
  COUNT(Id) total,
  SUM(AnnualRevenue) total_revenue,
  AVG(AnnualRevenue) avg_revenue,
  MAX(AnnualRevenue) largest,
  MIN(AnnualRevenue) smallest
FROM Account
WHERE AnnualRevenue != null
GROUP LOCAL BY Industry
ORDER BY total_revenue DESC

Multi-Level Grouping

-- Count by industry and type
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry, Type
ORDER BY Industry, Type

With HAVING Clause

-- Industries with more than 10 accounts
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP LOCAL BY Industry
HAVING COUNT(Id) > 10
ORDER BY cnt DESC

Date Functions in GROUP BY

Local GROUP BY supports 13 date functions for grouping by date/time components:

Calendar Functions

-- Accounts created per year
SELECT CALENDAR_YEAR(CreatedDate) year, COUNT(Id)
FROM Account
GROUP LOCAL BY CALENDAR_YEAR(CreatedDate)
ORDER BY year

-- Monthly breakdown
SELECT
  CALENDAR_YEAR(CreatedDate) year,
  CALENDAR_MONTH(CreatedDate) month,
  COUNT(Id)
FROM Account
WHERE CreatedDate = LAST_N_MONTHS:12
GROUP LOCAL BY
  CALENDAR_YEAR(CreatedDate),
  CALENDAR_MONTH(CreatedDate)
ORDER BY year, month

Supported Date Functions

Function	Returns	Example
CALENDAR_YEAR(date)	4-digit year	2025
CALENDAR_MONTH(date)	Month (1-12)	3 (March)
CALENDAR_QUARTER(date)	Quarter (1-4)	1 (Q1)
DAY_IN_MONTH(date)	Day (1-31)	15
DAY_IN_WEEK(date)	Day (1-7, Sun=1)	4 (Wednesday)
DAY_IN_YEAR(date)	Day (1-366)	74
DAY_ONLY(date)	Date without time	2025-03-15
HOUR_IN_DAY(datetime)	Hour (0-23)	14
WEEK_IN_MONTH(date)	Week (1-6)	3
WEEK_IN_YEAR(date)	ISO week (1-53)	11

Fiscal Date Functions (Limited Support)

Limited Support:

• FISCAL_MONTH(date) - Falls back to CALENDAR_MONTH
• FISCAL_QUARTER(date) - Falls back to CALENDAR_QUARTER
• FISCAL_YEAR(date) - Falls back to CALENDAR_YEAR

Fiscal functions require organization-specific fiscal year configuration which is not available in local processing. They use calendar equivalents with a console warning.

Aggregate Functions

Standard Aggregate Functions

All standard SOQL aggregate functions are supported:

SELECT
  Industry,
  COUNT(Id) total,              -- Count all records (including nulls)
  COUNT(AnnualRevenue) cnt,      -- Count non-null values
  COUNT_DISTINCT(OwnerId) owners, -- Count unique non-null values
  SUM(AnnualRevenue) sum_rev,    -- Sum (returns null if all nulls)
  AVG(AnnualRevenue) avg_rev,    -- Average (ignores nulls)
  MAX(AnnualRevenue) max_rev,    -- Maximum value
  MIN(AnnualRevenue) min_rev     -- Minimum value
FROM Account
GROUP LOCAL BY Industry

Extended Aggregate Functions

Local GROUP BY adds additional aggregate functions:

FIRST / LAST

Return the first or last value in the group:

SELECT
  Industry,
  FIRST(Name) first_name,
  LAST(Name) last_name,
  COUNT(Id)
FROM Account
GROUP LOCAL BY Industry

JOIN / JOIN_DISTINCT

Concatenate values with a separator:

-- Join account names with comma
SELECT
  Industry,
  JOIN(Name, ', ') account_names
FROM Account
GROUP LOCAL BY Industry

-- Join distinct types with pipe separator
SELECT
  Industry,
  JOIN_DISTINCT(Type, ' | ') types
FROM Account
GROUP LOCAL BY Industry

Syntax:

• JOIN(field) - Join with comma (default)
• JOIN(field, separator) - Join with custom separator
• JOIN_DISTINCT(field, separator) - Join unique values only

HAVING Clause

HAVING filters grouped results based on aggregate values or grouped fields.

HAVING on Aggregates

-- Industries with average revenue > $1M
SELECT Industry, AVG(AnnualRevenue) avg_rev
FROM Account
WHERE AnnualRevenue != null
GROUP LOCAL BY Industry
HAVING AVG(AnnualRevenue) > 1000000

HAVING on Grouped Fields

-- Exclude null industries
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry
HAVING Industry != null

Complex HAVING Expressions

-- Multiple conditions
SELECT
  Type,
  COUNT(Id) cnt,
  AVG(AnnualRevenue) avg_rev
FROM Account
WHERE AnnualRevenue != null
GROUP LOCAL BY Type
HAVING (COUNT(Id) > 20 OR AVG(AnnualRevenue) > 500000)
  AND Type != null
ORDER BY cnt DESC

HAVING can reference:

• ✅ Fields in GROUP BY clause
• ✅ Aggregate functions
• ✅ Date functions that are in GROUP BY
• ❌ Fields not in GROUP BY (will fail validation)

ORDER BY Clause

ORDER BY Grouped Fields

SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry
ORDER BY Industry

ORDER BY Aggregates

-- Order by count (descending)
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP LOCAL BY Industry
ORDER BY COUNT(Id) DESC

-- Or using alias
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP LOCAL BY Industry
ORDER BY cnt DESC

NULL Ordering

-- NULLs first
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry
ORDER BY Industry NULLS FIRST

-- NULLs last
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry
ORDER BY Industry NULLS LAST

Default NULL behavior:

• ORDER BY field ASC → NULLS LAST (nulls at the end)
• ORDER BY field DESC → NULLS FIRST (nulls at the beginning)

Multi-Column Sorting

SELECT Type, Industry, COUNT(Id) cnt
FROM Account
GROUP LOCAL BY Type, Industry
ORDER BY Type, cnt DESC

Formula Fields

Local GROUP BY supports formula fields with access to the $records variable containing all records in the group:

SELECT
  Industry,
  COUNT(Id) total,
  {{$records.length}} record_count,
  {{$records.filter(r => r.AnnualRevenue > 100000).length}} high_revenue_count
FROM Account
GROUP LOCAL BY Industry

The $records variable is an array of all records in that group, allowing complex calculations.

Limitations and Gotchas

Performance Limitations

All records are fetched locally:

• For 10,000 matching records → 10,000 records fetched
• For 1,000,000 matching records → 1,000,000 records fetched
• Large datasets may cause:
  • Slow query execution
  • High memory usage
  • Browser/Node.js timeout or crash

Best practice: Use WHERE clause to limit records before grouping

-- Good: Limit data first
SELECT Industry, COUNT(Id)
FROM Account
WHERE CreatedDate = LAST_N_DAYS:90
GROUP LOCAL BY Industry

-- Bad: Fetching millions of records
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry

Timezone Differences

Date functions use local timezone:

• JavaScript Date object uses browser/system timezone
• May differ from Salesforce org timezone
• Can cause date grouping discrepancies

Example issue:

Salesforce org timezone: PST (UTC-8)
Browser timezone: EST (UTC-5)
Record CreatedDate: 2025-01-01 02:00:00 UTC

Server GROUP BY: Groups as 2024-12-31 (PST)
Local GROUP BY: Groups as 2025-01-01 (EST)

Mitigation: Be aware of timezone differences when grouping by date/time

Type Coercion Differences

String vs numeric comparisons:

Local GROUP BY performs type coercion in HAVING:

HAVING COUNT(Id) = "10"  -- Returns true if count is 10 (coerces string to number)

This matches SOQL behavior but may be unexpected.

MAX/MIN on Mixed Data Types

String vs numeric comparison:

-- If AnnualRevenue contains both numbers and strings
SELECT MAX(AnnualRevenue)
FROM Account
GROUP LOCAL BY Industry

Local GROUP BY detects data type:

• If all values are numeric → numeric comparison
• If any value is non-numeric → string comparison (case-insensitive)

This may differ from Salesforce behavior if data types are mixed.

Empty String vs NULL

Empty strings and NULL are distinct:

SELECT BillingCity, COUNT(Id)
FROM Account
GROUP LOCAL BY BillingCity

Creates separate groups for:

• BillingCity = '' (empty string)
• BillingCity = null (NULL)

This matches SOQL behavior but is worth noting.

ROLLUP and CUBE Not Supported

These features require server-side processing:

-- ❌ Will fail with error
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP LOCAL BY ROLLUP(Industry, Type)

-- ❌ Will fail with error
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP LOCAL BY CUBE(Industry, Type)

Error message:

GROUP BY ROLLUP is not supported in local GROUP BY.
ROLLUP and CUBE require server-side processing for hierarchical aggregation.
Please remove the LOCAL keyword to use GROUP BY ROLLUP with standard SOQL execution.

Alternative: Run multiple queries or use standard GROUP BY without LOCAL

Fiscal Date Functions

Fiscal functions use calendar equivalents:

SELECT FISCAL_YEAR(CreatedDate), COUNT(Id)
FROM Account
GROUP LOCAL BY FISCAL_YEAR(CreatedDate)

• Returns calendar year instead of fiscal year
• Console warning displayed
• May produce incorrect results if org uses non-standard fiscal year

Workaround: Use standard GROUP BY (without LOCAL) for fiscal functions

Formula Field Evaluation

Formula fields are evaluated after grouping:

SELECT
  Industry,
  {{$records.length}} cnt
FROM Account
GROUP LOCAL BY Industry

• $records contains records in that group
• Formulas have access to grouped data
• More powerful than standard GROUP BY
• But may have performance implications for complex formulas

Validation Rules

Local GROUP BY enforces standard SOQL GROUP BY validation rules:

Comparison: Local vs Server GROUP BY

Feature	Server GROUP BY	Local GROUP BY
Syntax	GROUP BY	GROUP LOCAL BY
Processing	Salesforce servers	Your machine
Row limit	2000 rows	No limit*
Performance	Fast (server-side)	Slower (client-side)
Unsupported fields	Many restrictions	Fewer restrictions
Date functions	Some supported	13 supported
ROLLUP/CUBE	✅ Supported	❌ Not supported
Fiscal functions	✅ Fully supported	⚠️ Limited (calendar fallback)
Large datasets	✅ Efficient	⚠️ May cause issues
Timezone	Org timezone	Browser/system timezone

*Subject to memory and performance constraints

Best Practices

1. Use WHERE to Limit Data

Always use WHERE clause to reduce the number of records fetched:

-- Good
SELECT Industry, COUNT(Id)
FROM Account
WHERE CreatedDate = THIS_YEAR
GROUP LOCAL BY Industry

-- Avoid (fetches all records)
SELECT Industry, COUNT(Id)
FROM Account
GROUP LOCAL BY Industry

2. Test with Small Dataset First

Before running on production data:

1. Test query with LIMIT 1000 or narrow WHERE clause
2. Verify results match expectations
3. Check performance (execution time)
4. Then run on full dataset

3. Use Aliases for Readability

-- Good: Clear aliases
SELECT
  Industry ind,
  COUNT(Id) total,
  AVG(AnnualRevenue) avg_rev
FROM Account
GROUP LOCAL BY Industry
ORDER BY avg_rev DESC

-- Harder to read: No aliases
SELECT
  Industry,
  COUNT(Id),
  AVG(AnnualRevenue)
FROM Account
GROUP LOCAL BY Industry
ORDER BY AVG(AnnualRevenue) DESC

4. Prefer Server GROUP BY When Possible

Use Local GROUP BY only when necessary:

• Server GROUP BY is faster
• Server GROUP BY handles large datasets better
• Use Local GROUP BY to overcome specific limitations

5. Monitor Memory Usage

For very large datasets:

• Watch browser memory usage
• Consider breaking into smaller queries
• Use date ranges or other filters to reduce data

6. Be Aware of Timezone Issues

When grouping by dates:

• Document which timezone is used
• Consider timezone differences in results
• Test with records near timezone boundaries (midnight, etc.)

See Also

• SOQL GROUP BY Reference
• Aggregate Functions
• Date Functions

Feedback

If you encounter issues with Local GROUP BY, please send us the issue details and query.