SQL having instance tutorial

SQL having instance tutorial

Having
In the listen to have added to SQL, because where the keyword cannot use aggregate functions.

SQL with syntax

SELECT column_name, Aggregate_function (column_name) from
table_name
WHERE column_name operator Value
GROUP by column_name has
aggregate_function (column_name) operator value

Let's look at  an example of having one.

 
   
   

  
    
    
 
     
 
      
_id
 
      
OrderDate
 
      
Orderprice
 
      
Customer
 
     
 
     
 
      
1
 
      
2008/11/12
 
      
1000
 
      
Hansen
 
     
 
     
 
      
2
 
      
2008/10/23
 
      
1600
 
      
Nilsen
 
     
 
     
 
      
3
 
      
2008/09/02
 
      
700
 
      
Hansen
 
     
 
     
 
      
4
 
      
2008/09/03
 
      
300
 
      
Hansen
 
     
 
     
 
      
5
 
      
2008/08/30
 
      
2000
 
      
Jensen
 
     
 
     
 
      
6
 
      
2008/10/04
 
      
100
 
      
Nilsen
 
     
 
    
 
   
   

  
  

   
   

   
Now we are looking to see if there is any order for the total amount of the customer is less than 2000.

We use the following SQL statement:
   

   
   

   

   

   
   
SELECT Customer,sum (Orderprice) from the Orders GROUP by the
Customer having
SUM (orderprice) <2000

   
   


   
   
 
     
     

  
      
      
 
       
 
        
Customer
 
        
SUM (Orderprice)
 
       
 
       
 
        
Nilsen
 
        
1700
 
       
 
      
 
     
     

   
   
 
     
     
 
      
      
 Now we are looking for customers if "Hansen" or "Jason" has an orderly total of more than 1500. 
 
 We add a WHERE clause normal SQL statement: 
 
      
      
   
 
      
      
 SELECT customer,sum (orderprice) from Orders WHERE customer= ' Han

  Sen ' OR customer= ' Jensen ' GROUP by Customer has SUM (orderprice) >1500 
 
      
      
   
 
      
      
 results are. 
 
      
      
 
       
       
 
        
        
 
         
 
          
customer 
 
          
sum (orderprice) 
 
         
 
         
 
          
 Hansen 
 
          
 2000 
 
         
 
         
 
          
 Jensen