SQL SELECT HAVING Statement

SQL SELECT- HAVING statement  

 

SQL Having is a statement that specifies a search condition for a group or an aggregate. Having can be used only with the select statement. Having is typically used with a group by clause. When group by is not used, there is an implicit single, aggregated group. 

 

The Having statement enables you to specify conditions that filter which group result appear in the results.

 

The Where clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the Group By clause in Having a statement.

 

The Having statement filters records that work on summarized Group By results. The Having Statement applies to summarized group records, whereas Where applies to individual records, only the groups that meet the Having criteria will be returned. Having requires that a Group by clause is the present statement. Where and Having can be in the same query in a SQL Statement. 

 

A Having statement is like a Where clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the Where clause applies to individual rows. A query can contain both a Where clause and a Having clause in that case.

• The Where statement is applied first to the individual rows in the tables or table-valued objects in the Diagram pane. Only the rows that meet the conditions in the Where clause are grouped.
• The Having statement is then applied to the rows in the result set, only the groups that meet the Having conditions appear in the query output. You can apply a Having clause only to columns that also appear in the Group By statement or in an aggregate function.

The Having clause was added to SQL because the where keyword could not be used with aggregate functions in SQL Select statement.

 

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The following SQL statement lists  the OrderId, OrderName in OrderDetails. Only include OrderId with more than 1 OrderDetails.

 

Syntax

SELECT COUNT(OrderId), OrderName
FROM OrderDetails
GROUP BY OrderName
HAVING COUNT(OrderId) >1;

Example

 

 

The following SQL statement lists  the OrderId, OrderName in OrderDetails, sorted high to low (only include OrderId with more than 2 OrderDetails).

 

Syntax 

SELECT COUNT(OrderId), OrderName
FROM OrderDetails
GROUP BY OrderName
HAVING COUNT(OrderId) >2
ORDER BY COUNT(OrderName) DESC;

Example

 

Using Having works with Where clause

 

 The following SQL query shows a Having with a Where clause

 

Syntax

select EmpName, SUM(EmpSalary) as EmpSalary from Employedetail
WHERE EmpName='Smith' OR EmpName='Rahul' or EmpName ='Meths'
GROUP BY EmpName
HAVING SUM(EmpSalary) < 30000

Using join in Having statement 

 

The following SQL statement lists the employees that have registered more than 10 orders.

 

Syntax

SELECT OrderName COUNT(OrderId) as TotalOrders
FROM OrderDetails
Inner Join EmployeeDetail on Orderdetails.OrderName=EmployeeDetail.EmpId
GROUP BY OrderName
HAVING COUNT(EmpId) >10

The following SQL statement lists if the Employees "Davolio" or "Noida" have registered more than 10 orders.

 

Syntax

SELECT OrderName, OrderAddress COUNT(OrderId) AS NumberOfOrders
FROM OrderDetails
INNER JOIN EmployeeDetail  ON Orders.EmpID = Employees.EmpName
WHERE EmpName  = 'Davolio' OR EmpAddress  = 'Noida'
GROUP BY LastName
HAVING COUNT(OrderId) > 10;

Summary

 

In this article, you learned how to use a SQL Select Having statement with various options.