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 does not support grouping. You can't include fields with these field types in a GROUP BY clause. The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can include the field in a GROUP BY clause.

You must use a GROUP BY clause if your query uses a LIMIT clause and an aggregated function. For example, the following query is valid:

1swfobject.registerObject("clippy.valid_agg_fn_limit_group_by", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT Name, Max(CreatedDate)
18FROM Account
19GROUP BY Name
20LIMIT 5

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

1swfobject.registerObject("clippy.codeblock-1", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT MAX(CreatedDate)
18FROM 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 is no guarantee that the requested batch size will be the actual batch size. This is done to 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.

Force.com SOQL and SOSL Reference

Considerations When Using GROUP BY

GROUP BY and queryMore()

GROUP BY and Subtotals