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
  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 this article, you learned how to use a SQL Select Having statement with various options.