How To Use Having Clause In OraclePLSQL
Description
Having clause is also associated with grouping and aggregate function. This is an optional statement. Having clause follows the Group by clause.
Syntax
- Select
- Expression Aggregate function()
- from
- table_name
- where
- condition
- Group by
- expression
- Having
- having_condition;
Example
In the example, given below, we will use Sum function-
- select
- Emp_name,
- Sum(salary) As "Total salary"
- From
- Employee
- group by
- Emp_name
- having
- sum(salary)> 30000;
Example
In the example, given below, we will use Count function.
select Emp_name, count(*) As "Total salary"
from Employee
where salary < 50000
group by Emp_name
having count(*) < 5;
Example
In the example, given below, we will use Min function-
select Emp_name, Min(salary) As "Lowest Salary"
from Employee
Group By Emp_name
having Min(salary) < 35000;
Example
In the example, given below, we will use Max function.
select Emp_name, Max(Salary) As "Lowest Salary"
from Employee
Group By Emp_name
having Max(salary) > 45000;
Example
In the example, given below, we will use AVG function-
select Dept_no, AVG(Salary)
from Employee
Group By Dept_no
having Avg(salary) > 35000;
Summary
Thus, we learnt, having clause is also associated with grouping and aggregate function. Having clause follows Group by clause. We learnt how to use this clause in Oracle with the examples.