Introduction
In this chapter, we will learn about SQL SELECT- HAVING Statement in SQL with various options.
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 a group by is not used, there is an implicit single, aggregated group.
The Having statement enables you to specify conditions that filter which group results appear in the results.
The Where clause places conditions on the selected columns, whereasthe HAVING clause places conditions on groups created by the Group By the clause in Having a statement.
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 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 the next chapter, we will learn how to use a SQL Select Distinct statement with various options.