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.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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:

Department Total_Salary
IT 48000
Sales 42000

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:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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:

Department Number_Of_Employees
IT 2
Sales 2

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.


Tagged , , , , , , , , , , , . Bookmark the permalink.