HAVING Clause in Oracle SQL | Oracle SQL Tutorials -17

Hi,

I will explain HAVING Clause in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

The SQL HAVING Clause

HAVING Clause is used with aggregate functions and mission of HAVING Clause is just like WHERE Clause.

HAVING Syntax

HAVING syntax is as follows.

SELECT column_name(s) FROM table_name GROUP BY column_name(s) 
HAVING condition
ORDER BY column_name(s);

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) 
HAVING condition
ORDER BY column_name(s);

SQL> select count(department_id),salary from hr.employees group by salary having count(department_id)>2;

COUNT(DEPARTMENT_ID)     SALARY
-------------------- ----------
                   3      12000
                   3       9000
                   4      11000
                   4      10000
                   3       3900
                   3      10500
                   4       3800
                   6       3500
                   4       4100
                   4       4200
                   4       3600

11 rows selected.


SQL> select count(department_id),sum(salary) from hr.employees group by department_id,salary having sum(salary)>10000;

COUNT(DEPARTMENT_ID) SUM(SALARY)
-------------------- -----------
                   3       10800
                   1       15000
                   3       31500
                   2       18000
                   1       12500
                   2       46000
                   3       12300
                   2       20000
                   2       17000
                   1       10600
                   3       11400

COUNT(DEPARTMENT_ID) SUM(SALARY)
-------------------- -----------
                   1       11000
                   1       13008
                   5       17500
                   2       24000
                   1       13008
                   1       12000
                   4       16800
                   2       14400
                   1       14000
                   1       14500
                   1       13000

COUNT(DEPARTMENT_ID) SUM(SALARY)
-------------------- -----------
                   2       23000
                   3       33000
                   2       11600
                   2       16000

26 rows selected.

SQL>

the other example is as follows.

SQL> select manager_id,DEPARTMENT_NAME from hr.departments group by manager_id,DEPARTMENT_NAME having count(manager_id)>0;

MANAGER_ID DEPARTMENT_NAME
---------- ------------------------------
       100 Executive
       205 Accounting
       108 Finance
       203 Human Resources
       121 Shipping
       103 IT
       201 Marketing
       204 Public Relations
       200 Administration
       201 Computer Engineering
       114 Purchasing

MANAGER_ID DEPARTMENT_NAME
---------- ------------------------------
       145 Sales

12 rows selected.

SQL>

You can list the department which is minimum salary is greater than 10000 as follows.

SQL> select department_id,min(salary) from hr.employees group by department_id,salary having min(salary)>10000;

DEPARTMENT_ID MIN(SALARY)
------------- -----------
           80       15000
           80       10500
           80       12500
           90       23000
           80       10600
           70       11000
          100       13008
           80       12000
          110       13008
           30       12000
           20       14000

DEPARTMENT_ID MIN(SALARY)
------------- -----------
           80       14500
           80       13000
           80       11500
           80       11000

15 rows selected.

SQL>

Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.