SQL HAVING Clause
The HAVING clause allows us to specify conditions on the rows for each group in combination with the GROUP BY clause (see the GROUP BY clause), the HAVING clause is evaluated after rows are grouped.
Syntax
SELECT expression1, expression2… expression_n, aggregate_function (aggregate_expression)
FROM TableName
GROUP BY expression1…, expression_n;
HAVING condition(s);
In below example we will display the total for each SalesOrderID from SalesOrderDetail table that exceeds sum of LineTotal by 120000 in the database AdventureWorks2014.
SELECT SalesOrderID, SUM(LineTotal) AS Total, ModifiedDate FROM Sales.SalesOrderDetail WHERE SUM(LineTotal) > 120000 GROUP BY SalesOrderID, ModifiedDate ORDER BY SalesOrderID
This query will cause an error, because the WHERE clause allows filtering row by row and not on group.
We will change our query like this:
SELECT SalesOrderID, SUM(LineTotal) AS Total, ModifiedDate FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, ModifiedDate HAVING SUM(LineTotal) > 120000 ORDER BY SalesOrderID
That’s the result we want to have.
Note:
The WHERE clause is evaluated before rows are grouped, and The HAVING clause is evaluated after rows are grouped.
Here is another example with COUNT() function, we would like to see how many employees in each job title in descending order that exceeds 14 (see order by clause), the query is given below.
SELECT JobTitle, COUNT(*) AS Number FROM [HumanResources].[Employee] GROUP BY JobTitle HAVING COUNT(*) > 14 ORDER BY Number DESC
Reference: www.SQLServerLog.com
