SQL SELECT HAVING Statement

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 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
  1. SELECT column_name(s)      
  2. FROM table_name      
  3. WHERE condition      
  4. GROUP BY column_name(s)      
  5. HAVING condition      
  6. ORDER BY column_name(s);     
The following SQL statement lists the OrderId, OrderName in OrderDetails. Only include OrderId with more than 1 OrderDetails.
 
Syntax
  1. SELECT COUNT(OrderId), OrderName      
  2. FROM OrderDetails      
  3. GROUP BY OrderName      
  4. HAVING COUNT(OrderId) >1;    
Example
 
SQL SELECT HAVING Statement
 
The following SQL statement lists the OrderId, OrderName in OrderDetails, sorted high to low (only include OrderId with more than 2 OrderDetails).
 
Syntax 
  1. SELECT COUNT(OrderId), OrderName      
  2. FROM OrderDetails      
  3. GROUP BY OrderName      
  4. HAVING COUNT(OrderId) >2      
  5. ORDER BY COUNT(OrderName) DESC;      
Example
SQL SELECT HAVING Statement
 

Using Having works with Where clause

 
The following SQL query shows a Having with a Where clause
 
Syntax
  1. select EmpName, SUM(EmpSalary) as EmpSalary from Employedetail      
  2. WHERE EmpName='Smith' OR EmpName='Rahul' or EmpName ='Meths'      
  3. GROUP BY EmpName      
  4. HAVING SUM(EmpSalary) < 30000     

Using join in Having Statement 

 
The following SQL statement lists the employees that have registered more than 10 orders.
 
Syntax
  1. SELECT OrderName COUNT(OrderId) as TotalOrders      
  2. FROM OrderDetails       
  3. Inner Join EmployeeDetail on Orderdetails.OrderName=EmployeeDetail.EmpId       
  4. GROUP BY OrderName      
  5. HAVING COUNT(EmpId) >10   
The following SQL statement lists if the Employees "Davolio" or "Noida" have registered more than 10 orders.
 
Syntax
  1. SELECT OrderName, OrderAddress COUNT(OrderId) AS NumberOfOrders        
  2. FROM OrderDetails         
  3. INNER JOIN EmployeeDetail  ON Orders.EmpID = Employees.EmpName        
  4. WHERE EmpName  = 'Davolio' OR EmpAddress  = 'Noida'        
  5. GROUP BY LastName        
  6. HAVING COUNT(OrderId) > 10;  

Summary

 
In the next chapter, we will learn how to use a SQL Select Distinct statement with various options.
Author
Naresh Beniwal
610 3.3k 437.4k
Next » SQL SELECT DISTINCT Statement