SQL HAVING Clause

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

having1

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

having2

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

having3

Reference: www.SQLServerLog.com

CLICK HERE to watch live practical.

You may also like...

Leave a Reply

Your email address will not be published.