HAVING Clause in SOQL (Salesforce Object Query Language)

In Salesforce Object Query Language (SOQL), when we want to apply any condition in the query, we use the WHERE clause, and we can apply logic there. The HAVING clause is also used to apply conditions in the query, but we can use the HAVING clause in SOQL when we filter results that aggregate functions return.

In this Salesforce tutorial, we will learn about the HAVING clause in SOQL (Salesforce Object Query Language). In that, I will explain the functionality of the HAVING clause and how to use it in SOQL queries with different examples.

Table of Contents

What is the HAVING Clause in SOQL?

The HAVING clause in SOQL is used to filter aggregated query results. It is applied after the aggregation has been performed, making it an essential tool when you want to work with grouped data and apply conditions to aggregated values.

We need to use this clause with the GROUP BY clause to filter aggregated records based on aggregate functions such as COUNT(), SUM(), MAX(), MIN(), or AVG(). It allows us to apply conditions to groups of data after they have been aggregated. However, the HAVING clause cannot be used without a GROUP BY clause.

We can combine the WHERE and HAVING clauses together in a SELECT query. In this case, the WHERE clause is used first to filter individual records. The records are then grouped, aggregate calculations are performed, and the HAVING clause is used to filter the groups.

Syntax: Declaring HAVING Clause in SOQL Query

SELECT [fields], COUNT(Id) FROM [object] WHERE [conditions] GROUP BY [field] HAVING [aggregate_condition]

SELECT: The SELECT clause specifies the fields to retrieve in the query results.
Aggregate Function: To calculate summary values for each group, we can perform functions like 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 records that share common values in specified fields.
HAVING [condition]: In the HAVING clause, we can add a condition or logic on the aggregate function that we used to filter the grouped result.

What is the Difference Between WHERE and HAVING Clause in SOQL?

Till now, we have seen the WHERE and HAVING clauses. Both of these are used to apply conditions to SOQL queries. Now, we will understand the key difference between these two clauses.

WHERE Clause	HAVING Clause
The WHERE clause is used to apply conditions on individual records.	The HAVING clause is used to apply filtration or conditions on groups.
It does not allow working with aggregate functions.	It can work with aggregate functions.
The WHERE clause acts as a pre-filter.	The HAVING clause acts as a post-filter.
The GROUP BY clause comes after the WHERE clause.	The GROUP BY clause comes before the HAVING clause.
It can be used with any clause in an SOQL query.	It can be used only with a GROUP BY clause SOQL query.

Example: Use HAVING Clause in SOQL Query

First, we will see a basic example in which we will use the HAVING clause in the SOQL query and will understand what the use of the HAVING clause is.

We only want to retrieve account names associated with more than two related opportunities using the SOQL query.

So here, we will use the group by clause on the opportunity object to group the records by account IDs available in the opportunity object, and then, by using the HAVING clause, we will check whether it displays only those accounts that have more than 2 related opportunities.

In the SELECT clause, we fetched account ID, account name (using 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 depending on the 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 two opportunity records.

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

In the query result, you can see that in the first result, we have one account ID, Edge Communications, which has 4 related opportunities.

In this way, we can use the HAVING clause to add conditions on aggregated fields to filter the group in SOQL.

Example: Use WHERE Clause With HAVING Clause in SOQL Query

Now, let’s understand how to use a where clause with a having clause, and we will understand the difference between these two clauses to display the records in the query result. In the key comparison, we have seen that we can combine the WHERE clause to filter records before aggregation and use HAVING to filter aggregated results.

We will use the same example that we have seen above, but this time, we want to find and display only those accounts that are associated with more than 2 opportunities, and those opportunities should be in the closed-won stage.

First, we need to provide the fields that we want to retrieve and then add the count aggregate function so that we can display the number of records that we retrieved.

Then, we retrieve only those opportunity records that are closed, so here, we need to apply the filter condition on the record, so we will see the WHERE clause.

After that, we want to display only those account names that have more than 2 related opportunities, and here, we will display only closed won opportunities because, before grouping, we applied the WHERE clause to get only closed won opportunities.

SELECT Account.Name, COUNT(Id)  FROM Opportunity WHERE StageName = 'Closed Won' GROUP BY Account.Name HAVING COUNT(Id) > 3

Here, in the SELECT clause, we provided fields that can be grouped or aggregated, and in the COUNT() aggregation function, we provided an ID field. That means the count function returns the available number of records.

After that, we only want the opportunities that the stage is closed and won. For that, we used the WHERE clause, which is used to add conditions to records.

Then, the other condition is that we want to display account names that have more than two related opportunities. For that, we used the GROUP BY clause to group the opportunity records by account ID or name.

Lastly, to filter the group records, we need to use the HAVING clause. Using that, we added a condition on the aggregated field.

Use WHERE Clause With HAVING Clause in SOQL Query

In this way, we can use the WHERE clause with the HAVING clause to filter records before aggregation and use HAVING to filter aggregated results.

Example: Use Multiple Aggregate Conditions With HAVING Clause in SOQL Query

While using the HAVING clause, we can add multiple conditions to the aggregated fields. I will show you how to add multiple aggregate conditions with the HAVING clause in SOQL in the steps below.

For example, we want to find how many campaigns there are whose particular campaign types have revenue of more than 90000, and also, they should contain more than three records in their campaign types. We need to use multiple conditions in the HAVING clause to retrieve these records.

SELECT COUNT (Id), SUM (Actual_Revenue__c), Type FROM Campaign GROUP BY Type HAVING SUM (Actual_Revenue__c) > 90000 AND COUNT (Id) > 3

In the above SOQL query, we provided fields that we want to display, which can be grouped or aggregated. To display the revenue in the grouping of campaign types, we need to use the GROUP By clause and provide the field by which you want to group the records.

Now, to filter the grouped records, we need to use the HAVING clause, where we can add conditions on aggregate functions. So here we added a condition on the actual coast aggregate field, and also, we want to display only those records that have more than 3 in the campaign type.

In the image below, you can see that in the COUNT(Id) column, only more than 3 values are displayed, and in the actual revenue, only more than 90000 revenue is displayed.

Use Multiple Aggregate Condition With HAVING Clause in SOQL Query

In this way, we can add multiple aggregate conditions with the HAVING clause in the SOQL query.

Conclusion

I hope you have got an idea about the HAVING Clause in SOQL (Salesforce Object Query Language). In that, I have explained the functionality of the HAVING clause and its syntax. After that, we have seen how to use the HAVING clause in SOQL queries with different SOQL clauses and aggregate functions with examples and step-by-step explanations.

Then, we also see the aggregate function, which is used with the HAVING clause in SOQL. Using them, we will be able to optimize the data queries and handle large data sets efficiently. In the above examples, we have seen the execution of the aggregation functions such as SUM(), AVG(), COUNT(), MAX(), and MIN().