HAVING

HAVING is an optional clause that can be used in a SOQL query to filter results that aggregate functions return.

You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM(). The HAVING clause is similar to a WHERE clause. The difference is that you can include aggregate functions in a HAVING clause, but not in a WHERE clause.

Similar to a WHERE clause, the HAVING clause supports all the comparison operators, such as =, in conditional expressions, which can contain multiple conditions using the logical AND, OR, and NOT operators.

[HAVING havingConditionExpression]

havingConditionExpression specifies one or more conditional expressions using aggregate functions to filter the query results.

For example, you can use a GROUP BY clause to determine how many leads are associated with each LeadSource value with the following query:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource

However, if you are only interested in LeadSource values that have generated more than 100 leads, you can filter the results by using a HAVING clause. For example:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

This example query returns accounts with duplicate names.

SELECT Name, Count(Id)
FROM Account
GROUP BY Name
HAVING Count(Id) > 1

This example query is invalid as City is not included in the GROUP BY clause:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100 and City LIKE 'San%'

note: A HAVING clause can't contain any semi-join or anti-join subqueries.