HAVING

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.

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:

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

1SELECT LeadSource, COUNT(Name)
2FROM Lead
3GROUP BY LeadSource
4HAVING COUNT(Name) > 100

This example query returns accounts with duplicate names.

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

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

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