HAVING Clause in Oracle

Having clause in Oracle SQL restricts the result data based on conditions mentioned in it.

For each group segregated by GROUP BY clause, having clause can be applied to filter that data.

HAVING can be used without GROUP BY but the utilization is pointless according to me.

Learn Oracle Step by Step



  SELECT columns | expressions
    FROM table_name
   WHERE conditions
GROUP BY {columns | expressions}
  HAVING {conditions};
Parameters Explanation


  • Single or multiple columns


  • Expressions of the query


  • Single or multiple tables


  • Conditions to filter the data


  • Using HAVING with COUNT function
  SELECT dept_no, COUNT(*)
    FROM employees
GROUP BY dept_no
  HAVING COUNT(*) >= 3;

In this example, departments that have equal to or more than 3 employees are queried from the table employees.

  • Using HAVING with AVG function
  SELECT dept_no, AVG(salary)
    FROM employees
   WHERE dept_no = 10
GROUP BY dept_no
  HAVING AVG(salary) >= 2000;

Here, this query fetches employees having an average salary greater than $2000 per month belonging to department number 10.

  • Using subquery in HAVING
  SELECT emp_no, MIN(salary)
    FROM employees
GROUP BY emp_no
  HAVING MIN(salary) <= (SELECT AVG(salary) FROM employees);

In this query, employees having the salary less than or equal to the average salary of all employees are derived.

  • Arithmetic operation in HAVING
  SELECT emp_no, AVG(salary+commission)
    FROM employees
GROUP BY emp_no
  HAVING AVG(salary+commission) > 2000;

This query fetches employees having a salary and commission greater than $2000.

Please feel free to write us at connect@oraclemine.com or hioraclemine@gmail.com if you find mistakes or errors in any of the topics. Your suggestions to improve the course will help OracleMine and its readers.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.