What is the SQL HAVING clause?
The SQL HAVING clause is a clause that allows you to filter out groups based on some filtering criteria. The HAVING clause is a lot like the WHERE clause; the only difference is that the WHERE clause filters rows, while the HAVING clause filters groups.
For example, if we wanted to find only the Color values in the Production.Product table that had at least 30 products of that color, we would use the query:
SELECT Color, COUNT(*)
GROUP BY Color
HAVING COUNT(*) >=30
We are telling SQL to first find out how many products are associated with each color. Then, we are only returning those colors that had a count of at least thirty rows (products).
Unlike the WHERE clause, that takes a column as an input, the SQL HAVING clause requires an aggregate function before the comparison operator. Looking at another example, let’s find out which JobTitle values in HumanResources.Employees belong to least ten employees:
SELECT JobTitle, COUNT(*)
GROUP BY JobTitle
HAVING COUNT(*) >=10
We must always specify a GROUP BY clause when we use the HAVING clause. This makes sense if you consider what the SQL HAVING clause does. Since the HAVING clause filters groups, then it follows that we must first have groups specified.
Let’s look at one more example. Let’s find the SalesPersonID values that did at least $3 million in sales in the year 2006. Let’s also order our results by the total sales column in descending order.
SELECT SalesPersonID, SUM(TotalDue) AS [2006 Sales]
WHERE OrderDate BETWEEN '1/1/2006' AND '12/31/2006'
GROUP BY SalesPersonID
HAVING SUM(TotalDue) >=3000000
ORDER BY [2006 Sales] DESC
We have used every single SQL clause in this one query. This is the “complete” SQL SELECT statement from:
SELECT [Column1], [Column2],…, [Column N]
FROM [Table Name]
WHERE [Column Name] [logical/comparison operator] [filter criteria]
GROUP BY [Grouping Column(s)]
HAVING [AGGREGATE FUNCTION] [logical/comparison operator] [group filtering criteria]
ORDER BY [Column Name, Alias, Ordinal]
These are but a few of the ways that you can use the SQL HAVING clause. Feel free to leave a comment below if you have any other questions! Again, remember we are using the AdventureWorks database for all of these examples. Refer back to our introduction to SQL post for instructions on how to get started.
Complete the following practice problems based on the concepts learned in this lesson!
- Find all SalesPersonID values from Sales.SalesOrderHeader whose average sales price (TotalDue) exceeded $25,000.
- Find all SalesPersonID values from Sales.SalesOrderHeader whose average sales price (TotalDue) exceeded $30,000 for all sales with an OrderDate during the year 2006.
- Find all JobTitles, in the HumanResources.Employee, that have at least 25 employees.