Having Clause in Oracle

The HAVING clause in the Oracle database is used to restrict the group of rows returned by the GROUP BY clause whose condition is true. It is usually used with the GROUP BY clause.

Generally in the SELECT statement if we want to restrict rows in a table, then we are using the WHERE clause. But After the GROUP BY clause, we are not allowed to use the WHERE clause. To restrict the groups, ANSI/ISO provided a HEAVING clause, which is used just after the GROUP BY clause.

Syntax of HAVING clause in Oracle,

SELECT expressions
FROM tableName
[ WHERE condition ]
GROUP BY expressions
[ HAVING group_condition ];

Note:- Group functions are not allowed in WHERE clause but it is allowed in HAVING clause.

Now, let’s understand the HAVING clause through pre-defined emp table of Scott user in Oracle database.

SQL> SELECT empno, deptno, sal, hiredate FROM emp;

     EMPNO     DEPTNO        SAL HIREDATE
---------- ---------- ---------- ---------
      7369         20        800 17-DEC-80
      7499         30       1600 20-FEB-81
      7521         30       1250 22-FEB-81
      7566         20       2975 02-APR-81
      7654         30       1250 28-SEP-81
      7698         30       2850 01-MAY-81
      7782         10       2450 09-JUN-81
      7788         20       3000 19-APR-87
      7839         10       5000 17-NOV-81
      7844         30       1500 08-SEP-81
      7876         20       1100 23-MAY-87
      7900         30        950 03-DEC-81
      7902         20       3000 03-DEC-81
      7934         10       1300 23-JAN-82

14 rows selected.

Examples of Oracle HAVING Clause

Example1) Below query display the deptno with sum of sal of dept from emp table.

SQL> SELECT deptno, sum(sal)
     FROM emp
     GROUP BY deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

Now, we want to restrict the group only display those department having sum(sal) > 9000. In this case below query used to display the deptno from emp table where the sum of sal of the dept is more than 9000.

SQL> SELECT deptno, sum(sal)
     FROM emp
     GROUP BY deptno
     HAVING sum(sal) > 9000;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875

Example2) Write a query to display the jobs having more than 5000 total salary from emp table.

Sum of sal of each jobs,

SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY job;

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         6000

Restricting the group by using HAVING clause,

SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY job
     HAVING SUM(sal) > 5000;

JOB         SUM(SAL)
--------- ----------
SALESMAN        5600
MANAGER         8275
ANALYST         6000

Example3) Display those department (deptno) having more than 4 employess from emp table using GROUP BY clause.

SQL> SELECT deptno, count(*)
     FROM emp
     GROUP BY deptno
     HAVING count(*) > 4;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

Example4) Display those department, sum(sal) and number of employees having more than 4 employess and sum of sal of the dept is more than 9000 from emp table using GROUP BY clause.

SQL> SELECT deptno, SUM(sal), COUNT(*)
     FROM emp
     GROUP BY deptno
     HAVING SUM(sal) > 9000 AND COUNT(*) > 4;

    DEPTNO   SUM(SAL)   COUNT(*)
---------- ---------- ----------
        30       9400          6
        20      10875          5

Example5) Write a query to display number of employess with year from emp table in which year more than 1 employess were hired using HAVING clause.

SQL> SELECT TO_CHAR(hiredate, 'YYYY'), COUNT(*)
    FROM emp
    GROUP BY TO_CHAR(hiredate, 'YYYY')
    HAVING COUNT(*) > 1;

TO_C   COUNT(*)
---- ----------
1987          2
1981         10

In all databases, we can also use HAVING clause in invisible functions because whenever we are using GROUP BY clause based on group function then all group functions are internally available.

Example6) Write a query to display deptno, and sum of salary of all employees belongs to that deptno. But the deptno having at least 3 employees.

SQL> SELECT deptno, sum(sal)
     FROM emp
     GROUP BY deptno
     HAVING count(*) > 3;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search