HAVING Clause

The Oracle HAVING clause is used in conjunction with the Oracle GROUP BY clause to limit the returned rows after the grouping.

SQL statements can utilize both a WHERE clause and an Oracle HAVING clause. The WHERE clause will filter rows as they are selected from the table, and before grouping, the Oracle HAVING clause will filter rows after the grouping.

Some related examples.

1. Example using the MAX function with having clause.

SQL> SELECT JOB, MAX(sal) as "Highest salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING MAX(sal) < 50000;

JOB       Highest salary
--------- --------------
CLERK               1300
SALESMAN            1600
PRESIDENT           5000
MANAGER             2975
ANALYST             3000

2. Using the HAVING Clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  GROUP BY City
  4  HAVING AVG(Salary) > 500;

CITY       Average Salary
---------- --------------
New York          6110.28
Toronto           1234.56
Vancouver         3823.78

3. Any conditions based on the outcome of a group function must be in the HAVING clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  WHERE City ='Vancouver'
  4  GROUP BY City
  5  HAVING AVG(Salary) > 1000;

CITY       Average Salary
---------- --------------
Vancouver         3823.78

4. Using the SUM function in HAVING Clause.

SQL> SELECT job, SUM(sal) as "Total salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING SUM(sal) > 1000;

JOB       Total salary
--------- ------------
CLERK             4150
SALESMAN          5600
PRESIDENT         5000
MANAGER           8275
ANALYST           6000

5. Using HAVING with an Analytical Function.

SQL> -- if a HAVING clause is added, 
SQL> -- it will have its effect just before the RANK.
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
  2    MIN(orig_salary) minsalary
  3    --   RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  4  FROM employee e, job j
  5  WHERE e.orig_salary < 43000
  6    AND e.empno = j.empno
  7  GROUP BY j.jobtitle
  8  HAVING MAX(orig_salary) > 34000
  9  ORDER BY j.jobtitle desc;

no rows selected

6. Sub query inside having clause.

SQL> SELECT count(*), city
  2  FROM employee
  3  GROUP BY city
  4  HAVING count(*) =
  5  (SELECT max(count(*))
  6  FROM employee
  7  GROUP BY city);

  COUNT(*) CITY
---------- ----------
         5 Vancouver