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.

GROUP BY Statement in Oracle SQL | Oracle SQL Tutorials -16

 

 

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);
HAVING examples are as follows.
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.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *