1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL HAVING statement

Discussion in 'SQL PL/SQL' started by SBH, Oct 16, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article explains how to restrict GROUP BY results with the use of HAVING clause.

    1. HAVING Clause

    GROUP BY clause groups the query result set and produces aggregated results within the group like count, sum, maximum and minimum. For cases, where these results have to be restricted or filtered, it can’t be done through WHERE clause. Filtering of Grouped Results is done by HAVING clause. This clause is exclusively used to filter grouped results. Therefore, for a HAVING clause, the query result must be grouped.

    Code (SQL):
    SQL> SELECT
    DEPARTMENT_ID,
    SUM(SALARY)
    FROM EMPLOYEE
    WHERE SUM(SALARY) > 5000;


    WHERE SUM(SALARY) > 5000
          *
    ERROR at line 3:
    ORA-00934: GROUP FUNCTION IS NOT allowed here
    It follows the Group by clause in the SELECT statement. But it can also precede the Group by clause (not logical and not recommended).

    Syntax

    Code (Text):
    SELECT <column list>, <group by function>
    FROM <table name>
    WHERE <conditions>
    GROUP BY <column list>
    HAVING <group by function condition>
    Examples:

    Example 1. Below SQL query shows the department and sum of salaries in the department, if it exceeds 10000.



    Code (SQL):
    SQL> SELECT
    JOB_ID,
    SUM (SALARY)
    FROM EMPLOYEE
    GROUP BY JOB_ID
    HAVING SUM (SALARY) > 10000;

    JOB_ID  SUM(SALARY)
    --------    -------------------
    MGR     12000
    DEV     23500
    HR      14000
    ADM     17000



     
    Example 2. Below SQL query shows departments with more than 50 employees.


    Code (SQL):
    SQL>SELECT
    DEPARTMENT_ID,
    COUNT (*)
    FROM EMPLOYEE
    GROUP BY DEPARTMENT_ID
    HAVING COUNT (*) > 50
    Result of above query is depicted with the help of below figure.

    [​IMG]

    Example 3. Below SQL query shows the departments where total salary is more than the average salary of that department.

    Code (SQL):
    SQL>SELECT
    DEPARTMENT_ID,
    SUM (SALARY),
    AVG (SALARY)
    FROM EMPLOYEE
    GROUP BY DEPARTMENT_ID
    HAVING SUM (SALARY) > AVG(SALARY)

    HAVING clause is a great filtering tool and plays crucial role in large reporting applications, where aggregated results are required for business forecasting and future insights.


    References
     

    Attached Files: