GROUP BY Clause in SOQL (Salesforce Object Query Language)

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.

Table of Contents

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.

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

Example: GROUP BY Multiple Fields in SOQL

Now, in the SOQL query below, I retrieved account records with industry and billing country grouping fields. In a group of multiple records, the result will display the first industry,

the billing city, and the record count. After that, the same sector and another billing city. Like this, it will display the groupwise record count.

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

In this way, we can use the GROUP BY clause in SOQL to group records by multiple fields.

Example: GROUP BY Clause With Aggregate Functions in SOQL

In SOQL, we can use grouping and aggregate functions to summarize and analyze data. Grouping enables us to organize records by specific fields and apply aggregate functions. Below, I have explained the aggregate function used in SOQL.

COUNT(): Counts the number of records in each group.
SUM(): Adds up all the values in a numeric field for each group.
AVG(): Calculates the average value of a numeric field for each group.
MIN(): Finds the smallest value in a field for each group.
MAX(): Finds the largest value in a field for each group.

For example, we want to fetch records from the account object and display the record count, total annual revenue, and minimum amount generated, depending on the accounting industry, to analyze the yearly revenue generated by those groups of particular accounts.

SELECT Industry, COUNT(ID), SUM(AnnualRevenue), MIN(Sum_of_Amounts__c) FROM Account GROUP BY Industry

In the above SOQL query, I used three aggregate functions in the GROUP BY clause. We retrieved the account records grouped by the industry field, and depending on the particular industry, we used the COUNT() function to display the number of records in that industry.

Then, using the SUM() function, we displayed the annual revenue of all grouped records, and the same was true for the MIN() function.

In the result, first, we have the Hospitality industry. We applied the group by clause to the industry field, so under the hospitality industry, we have one record.

Then, the total sum of annual revenue and the minimum sum of amounts are displayed using the aggregate functions and the GROUP BY clause in SOQL.

In this way, we can use the GROUP BY clause with aggregate functions in SOQL to summarize and analyze data.

Example: Use HAVING in GROUP BY Clause in SOQL

In SOQL, the HAVING clause is used with the GROUP BY clause to filter groups of records based on aggregate functions.

It allows us to specify conditions for groups created by the GROUP BY clause rather than filtering individual records, which is done with the WHERE clause. It is often used in conjunction with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Syntax:

SELECT <fields>, COUNT(field) FROM <object> GROUP BY <field> HAVING <condition_on_aggregate_function>

We only want to retrieve account records that have more than 2 related opportunities using the SOQL query.

Here, we will use the GROUP BY clause on the opportunity object to group the records by account IDs available in the opportunity object. Then, using the HAVING clause, we will check whether to display only those accounts that have more than 2related opportunities.

In the SELECT clause, we retrieved the account ID, account name (utilizing a child-to-parent relationship in SOQL), and the COUNT() function. We passed the opportunity ID to the count function, which means it will return the number of opportunity records.

After that, we used the GROUP BY clause, which groups the opportunity records by account ID. This query will return the opportunity record count grouped by a particular account ID.

However, we added a HAVING clause, which is used to add conditions to the grouped records. Here, using the HAVING clause, we applied the condition to display only those accounts with more than 2 opportunity records.

SELECT AccountId, Account.Name, COUNT(Id) FROM Opportunity GROUP BY AccountId, Account.Name HAVING COUNT(Id) > 2

In the query result, you can see that the first result includes one account ID, Edge Communications, which has four related opportunities.

Example: GROUP BY LIMIT in SOQL

The GROUP BY LIMIT clause in SOQL is used to group query results and limit the number of groups returned. It is particularly useful when we want to aggregate data, find the most or least common values, or focus on a subset of grouped data.

For example, you are fetching the record from the Salesforce database. If there are a number of records and you want to display them by grouping them by a particular field and display only a limited number of records, then we need to use GROUP BY LIMIT in SOQL.

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

Example: Use ORDER BY with GROUP BY Clause in SOQL

In Apex SOQL, the ORDER BY clause can be used with the GROUP BY clause to sort the aggregated results of the SOQL query. When we use GROUP BY, we group records by a particular field or set of fields.

The ORDER BY clause allows us to specify the order in which these grouped results should be displayed in the list.

If you don’t add any order in the ORDER BY clause, then it will ascend by default. When we want to display the results in ascending order, we only need to apply ORDER BY and provide a field.

Syntax:

SELECT field1, COUNT(Id) FROM ObjectName GROUP BY field1 ORDER BY field1 DESC

For example, we want to display opportunity records and analyze which have the highest record counts depending on the stages.

In the SOQL query below, we retrieved the stage name and opportunity record count by ID. Then, using the GROUP BY clause, we grouped records by stage name. To display the grouped fields in descending order by record count, we used the ORDER BY clause.

SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName ORDER BY COUNT(Id) DESC

Conclusion

I hope you have got an idea about the GROUP BY Clause in SOQL (Salesforce Object Query Language). In that, I have explained the functionality of the GROUP BY clause, its syntax, and how to use it in SOQL queries with different examples.

DOWNLOAD FREE AGENTFORCE EBOOK

FREE SALESFORCE FLOW EBOOK