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(). 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. 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");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT LeadSource, COUNT(Name)
18FROM Lead
19GROUP 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");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT LeadSource, COUNT(Name)
18FROM Lead
19GROUP BY LeadSource
20HAVING COUNT(Name) > 100

The next query returns accounts with duplicate names:

1swfobject.registerObject("clippy.dup_account_query", "9");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT Name, Count(Id)
18FROM Account
19GROUP BY Name
20HAVING Count(Id) > 1

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