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