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

HAVING CLAUSE

Syntax

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

Parameters Explanation

columns

• Single or multiple columns

expressions

• Expressions of the query

table_name

• Single or multiple tables

conditions

• Conditions to filter the data

Example

• 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.

Paras Shah

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!