HAVING

With API version 18.0 and later, you can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM(). A 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. The syntax is:

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

1swfobject.registerObject("clippy.codeblock-1", "9");SELECT LeadSource, COUNT(Name)
2FROM Lead
3GROUP 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:

1swfobject.registerObject("clippy.having_sample_query", "9");SELECT LeadSource, COUNT(Name)
2FROM Lead
3GROUP BY LeadSource
4HAVING COUNT(Name) > 100

The next query returns accounts with duplicate names:

1swfobject.registerObject("clippy.dup_account_query", "9");SELECT Name, Count(Id)
2FROM Account
3GROUP BY Name
4HAVING Count(Id) > 1

For a list of aggregate functions supported by SOQL, see Aggregate Functions.