HAVING

HAVING further filters the result based on values in the results, rather than the actual data. A HAVING clause only applies to columns that are included in the GROUP BY clause or in an aggregate function. Building on the same example used in the WHERE, ORDER BY, and GROUP BY sections, here we want to additionally know which locations carry an inventory of over 100 items per product. For that, after the GROUP BY clause, the query has a HAVING clause over the aggregate function, where its result is greater than 100:

SELECT P.Name AS ProductName, SUM([PI].Quantity) AS Total_Quantity, L.Name AS LocationName
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS [PI] ON P.ProductID = [PI].ProductID
INNER JOIN Production.Location AS L ON [PI].LocationID = L.LocationID
WHERE P.Name LIKE 'Touring%'
GROUP BY P.Name, L.Name
HAVING SUM([PI].Quantity) > 100
ORDER BY P.Name DESC, L.Name DESC;

The following screenshot shows the results as containing only rows with an aggregate Total_Quantity greater than 100: