Salesforce logSalesforce

Pages

GROUP BY

You can use the GROUP BY option in a SOQL query to avoid iterating through individual query results. That is, you specify a group of records instead of processing many individual records.

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:

[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:

SELECT 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:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP 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.

SELECT LeadSource
FROM Lead
GROUP BY LeadSource

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