Friday, November 30

SQL- Having Clause



Having Clause

Having clause is used to filter records in result-set similar to where clause, But you can’t use aliases in Having clauses,you have to repeat the original expression. A Where clause must come before a Group By clause and a Having clause has to come after the Group By.The two clauses operate differently. A Where clause does not return unwanted rows, They are filtered out at source. A Having clause has to return the records so the grouping and aggregating can be done before they are filtered out. Where clause can't be used with aggregate functions thus Having clause is used.


Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value        // acc. to result-set required,where is 

                                           used 
GROUP BY column_name
HAVING aggregate_function(column_name) operator value   
    //original aggregate
                                                              expression is used
                                                                                                        
Example:
Consider the following Item_order table
Now you want to check whether any customer has order Total_price less than 15000. By using the below query we can find that.

Query 1:
        SELECT Cust_Name,SUM(Price) as Total_Price FROM   Item_order
        GROUP BY Cust_Name
        HAVING Total_Price <15000


Result:Error,Invalid column name 'Total_Price'. Because you can’t use aliases in Having clauses,you have to repeat the original expression. Now see the corrected query.

Query 2:
        SELECT Cust_Name,SUM(Price) as Total_Price FROM Item_order
        GROUP BY Cust_Name
        HAVING SUM(Price) <15000


Result:
Now if you want to find the customers "John" or "Rahul" have a order Total_Price of more than 20000. Just add a where clause, See the below query.

Query 3:
       SELECT Cust_Name,SUM(Price) as Total_Price FROM  Item_order
       WHERE Cust_Name='John' OR Cust_Name='Rahul'
       
GROUP BY  Cust_Name
       
HAVING SUM(Price)>20000

Result: