SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
Condition Expression Syntax (WHERE Clause)
fieldExpression Syntax
USING SCOPE
ORDER BY
LIMIT
OFFSET
FIELDS()
Update an Article’s Keyword Tracking with SOQL
Update an Article Viewstat with SOQL
WITH filteringExpression
GROUP BY
Considerations When Using GROUP BY
Using Aliases with GROUP BY
GROUP BY ROLLUP
Using GROUPING(fieldName) to Identify Subtotals
GROUP BY CUBE
HAVING
TYPEOF
FORMAT ()
FOR VIEW
FOR REFERENCE
FOR UPDATE
Aggregate Functions
Date Functions
Querying Currency Fields in Multi-Currency Orgs
Example SELECT Clauses
Relationship Queries
Change the Batch Size in Queries
SOQL Limits on Objects
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Async SOQL
Salesforce Object Search Language (SOSL)
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Considerations When Using GROUP BY

When you’re creating SOQL queries with the GROUP BY clause, there are some considerations to keep in mind.
  • Some object fields have a field type that doesn’t support grouping. You can't include fields with these field types in a GROUP BY clause. To check whether a field supports grouping, you can use SOAP API or REST API to make a describe call for the object that contains the field. The response includes a groupable field in the Field object that defines whether you can include the field in a GROUP BY clause. To check whether a field supports grouping for standard objects, you can also check the properties for the field in the Object Reference for Salesforce and Lightning Platform
  • Use a GROUP BY clause if your query has a LIMIT clause and an aggregated function. For example, the following query is valid:
    1SELECT Name, Max(CreatedDate)
2FROM Account
3GROUP BY Name
4LIMIT 5

    The following query is invalid as there’s no GROUP BY clause:

    1SELECT MAX(CreatedDate)
2FROM Account LIMIT 1
  • You can't use child relationship expressions that use the __r syntax in a query that uses a GROUP BY clause. For more information, see Understanding Relationship Names, Custom Objects, and Custom Fields.

GROUP BY and queryMore()

For queries that don't include a GROUP BY clause, the query result object contains up to 500 rows of data by default. If the query results exceed 500 rows, then your client application can use the queryMore() call and a server-side cursor to retrieve additional rows in 500-row chunks.

However, if a query includes a GROUP BY clause, you can't use queryMore(). You can increase the default size up to 2,000 in the QueryOptions header. If your query results exceed 2,000 rows, you must change the filtering conditions to query data in smaller chunks. There’s no guarantee that the requested batch size is the actual batch size. This behavior helps maximize performance. See Change the Batch Size in Queries for more details.

GROUP BY and Subtotals

If you want a query to do the work of calculating subtotals so that you don't have to maintain that logic in your code, see GROUP BY ROLLUP. If you want to calculate subtotals for every possible combination of grouped field (to generate a cross-tabular report, for example), see GROUP BY CUBE instead.