[SalesForce] What types of fields are groupable in a SOQL `GROUP BY` clause

The Salesforce documentation is notably terse in describing Considerations When Using GROUP BY. The guidance provided for determining which fields can be grouped is simply:

The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can include the field in a GROUP BY clause.

Further, the document states that

You can't use child relationship expressions that use the __r syntax in a query that uses a GROUP BY clause.

Which field types in fact permit grouping? Does the final sentence prohibit the use of custom relationships in GROUP BY? Multiple previous questions have not yielded a conclusive statement.

Failure to use a properly groupable field yields the error

field 'FIELD_NAME__c' can not be grouped in a query call

Best Answer

Groupability breaks down pretty cleanly along type lines, with a few interesting nuances. The underlying SOAP type appears to be the primary determinant, and some formula fields can be used as groupings.

Types are listed below by UI type, with the SOAP type in parentheses. This information was derived from inspection of numerous field describes via Workbench and the Tooling API.

Groupable Field Types

  • Checkbox (boolean)
  • Phone (string)
  • Picklist (string)
  • Email (string)
  • Text (string)
  • Text Area (string)
  • URL (string)
  • Number (int). Does not include custom fields, only standard Number fields with SOAP type int, like Account.NumberOfEmployees.
  • Lookup (id)
  • Master Detail (id)
  • Id (id)
  • Date (date)
  • Direct cross-object references to groupable fields, up to 5 levels from the root object (SOQL limit), as in SELECT count(Id) FROM Contact GROUP BY Account.Parent.Parent.Parent.Parent.Name. Both custom and standard references are groupable.
  • Formulas of type Checkbox and Date, including cross-object formulas across standard and custom relationships.

Non-Groupable Field Types

  • Address Compound Fields
    • Components of Address compound fields are groupable if their types otherwise allow it.
  • Geolocations, both custom and standard, and whether or not defined as having decimal places, including the compound field and components (location/double)
  • Long Text (string)
  • Rich Text (string)
  • Auto Number (string)
  • Multi-Select Picklist (string)
  • Number (double), including custom Number fields with or without decimal and regardless of scale.
  • Percent (double), including custom Percent fields with or without decimal and regardless of scale.
  • Currency (double), including custom Currency fields with or without decimal and regardless of scale.
  • Roll-Up Summary Fields (double), including COUNT rollups.
  • Encrypted Text Fields (Classic Encryption; string)
  • Encrypted fields that use the probabilistic encryption scheme can’t be used with the SOQL and SOSL GROUP BY clauses
  • Date/Time (dateTime)
  • Time (time)
  • Formulas of types other than Checkbox and Date, including the otherwise-groupable String type.
Related Topic