Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • What is the use of HAVING Clause in MySQL?

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 93
    Comment on it

    The HAVING clause is used with aggregate functions as the WHERE clause can not be used with them.

    HAVING Syntax

    SELECT column_name, aggregate_function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name
    HAVING aggregate_function(column_name) operator value;
    

    We have a table "employee" as below:

        employee
        id  first_name            last_name       salary
        .......................................................
        1   John                     Simp         10000
        2   Chris                    Hely         25000
        3   John                     Roy          20000
        4   Jenny                    Mill         35000
        5   Jenny                    Simpson      35000
    

    HAVING Example

    Now we want to find all the records which have duplicate values in column "first_name" then you just need to write the below command:

    SELECT first_name, COUNT(first_name) as count 
    FROM employee
    GROUP by first_name 
    HAVING COUNT(first_name) > 1;
    

    Result:

        first_name            count
        .............................
        John                  2
        Jenny                 2
    

    Now we want to find the records which have duplicate values in column "first_name" and salary is 35000, then you just need to write the below command:

    SELECT first_name, COUNT(first_name) as count 
    FROM employee
    WHERE salary = 35000
    GROUP by first_name 
    HAVING COUNT(first_name) > 1;
    

    Result:

        first_name            count
        .............................
        Jenny                  2
    

    Hope this will help you :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: