HAVING Clause in Oracle SQL – PLSQL

The HAVING clause in Oracle SQL / PLSQL is used in SELECT statement along with the GROUP BY clause and allows us to filter the records fetched by GROUP BY clause based on one or more than one condition.

Syntax for the HAVING clause in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
,column_name3
.
.
column_nameN
Aggregate_function(value/expression)
FROM table_name
WHERE conditions
GROUP BY column_name1
,column_name2
.
.
column_nameN
HAVING condition1
,condition2
.
.
conditionN;

The aggregate function can be SUM(), MIN(), MAX() or COUNT().

Let’s take an example for understanding:

Suppose have a table named ‘employee’ in the database as shown below.

Scenario 1:

Suppose we want to see how much ‘Salary’ is being given to which department, but the total ‘Salary’ being given to a department should be more than ‘30000’.

We can achieve the same as:

SELECT department
       ,SUM(Salary) Total_Salary
FROM employee
GROUP BY department
HAVING SUM(salary) > 30000;

The result of the above query will be:

Here we have retrieved the records explaining us that ‘48000’ in total is being given to employee(s) working in the ‘IT’ department and ‘42000’ is being given to employee(s) working in the ‘Sales’ department by using HAVING clause with GROUPY BY clause in SELECT statement.

Also, note that ‘Total_Salary’ after the aggregate function ‘SUM(salary)‘ acts as an alias name for the column in query result.

Scenario 2:

Suppose we want to see the departments where more than ‘1’ employees are working.
Let’s assume that we have a new table namely ‘employee’ as shown below:

We can achieve the same as:

SELECT department
       ,COUNT(employee_id) Number_Of_Employees
FROM employee
GROUP BY department
HAVING COUNT(employee_id) > 1;

Once we have run the above code following will be the result:

Here we have successfully retrieved the records pertaining to departments where more than ‘1’ employees are working using HAVING clause with GROUP BY clause in SELECT statement.

Also, note that ‘Number_Of_Employees’ after the aggregate function ‘COUNT(employee_id)‘ acts as an alias name for the column in query result.