GROUP BY Clause in SOQL (Salesforce Object Query Language)

Updated by

When you have many records in Salesforce objects and from lead objects, you want to calculate how many leads are generated from the web using an SOQL query in Apex.

For that, in SOQL, we can use the group by clause to calculate the number of records from a particular group.

In this Salesforce tutorial, we will learn about the GROUP BY Clause in SOQL (Salesforce Object Query Language). In this context, I will explain the functionality of the GROUP BY clause and demonstrate its use in SOQL queries with various examples.

What is the GROUP BY Clause in SOQL?

The GROUP BY clause in Salesforce Object Query Language (SOQL) is used to group rows that share common values in specified fields.

It is similar to the SQL GROUP BY clause and is often used in conjunction with aggregate functions, such as COUNT(), SUM(), MAX(), or MIN(), to calculate summary values for each group.

Important: Only fields available in the SELECT statement or aggregated results can be grouped.

Below, I have explained the functionality of the GROUP BY clause in SOQL:

  • Using the group by clause, we can group the rows based on the values in specified fields. For example, grouping accounts by Industry will create separate groups for each industry type.
  • We can use the group-by-clause with aggregate functions to perform calculations, such as counting records or summing values, for each group.
  • This clause enables grouping query results by multiple fields, allowing us to retrieve records with various fields in a single group.

Syntax:

SELECT field1, aggregate_function (field) FROM object WHERE condition GROUP BY field1
  • SELECT: The SELECT clause specifies the fields to retrieve in the query results.
  • Aggregate Function: To calculate summary values for each group, we can use functions such as COUNT(), SUM(), MAX(), or MIN().
  • FROM: The FROM clause defines the Sobject from which to retrieve data.
  • WHERE (Optional): The WHERE clause filters the query results based on specific conditions, as you specify.
  • GROUP BY: Group rows that share common values in specified fields.

Example: GROUP BY Records with COUNT() in SOQL

When we want to know the account records count for a particular Industry, we can create a SOQL query with a group-by clause, grouping by the industry field. Then, using the count() function, we can get the number of records from different industries.

SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry

After executing the above query, the result is displayed in the industry grouping, and we also get the number of records present in that particular industry group.

GROUP BY Clause in Apex SOQL

In this way, we can use the GROUP BY clause with the COUNT() function in SOQL to group records and display their counts.