GROUP BY

With API version 18.0 and later, you can use GROUP BY with aggregate functions, such as SUM() or MAX(), to summarize the data and enable you to roll up query results rather than having to process the individual records in your code. The syntax is:

1[GROUP BY fieldGroupByList]

fieldGroupByList specifies a list of one or more fields, separated by commas, that you want to group by. If the list of fields in a SELECT clause includes an aggregate function, you must include all non-aggregated fields in the GROUP BY clause.

For example, to determine how many leads are associated with each LeadSource value without using GROUP BY, you could run the following query:

1swfobject.registerObject("clippy.codeblock-1", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT LeadSource FROM Lead

You would then write some code to iterate through the query results and increment counters for each LeadSource value. You can use GROUP BY to get the same results without the need to write any extra code. For example:

1swfobject.registerObject("clippy.sample_group_by_soql", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT LeadSource, COUNT(Name)
18FROM Lead
19GROUP BY LeadSource

For a list of aggregate functions supported by SOQL, see Aggregate Functions.

You can use a GROUP BY clause without an aggregated function to query all the distinct values, including null, for an object. The following query returns the distinct set of values stored in the LeadSource field.

1swfobject.registerObject("clippy.distinct_group_by_sample", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT LeadSource
18FROM Lead
19GROUP BY LeadSource

Note that the COUNT_DISTINCT() function returns the number of distinct non-null field values matching the query criteria.