Oracle HAVING Clause

➔ The HAVING clause is an optional clause in the SELECT statement.

➔ This clause allows you to apply conditions to the GROUP function to filter groups created by the GROUP BY clause.

➔ The HAVING clause is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() to filter groups created by the GROUP BY clause.

➔ This optional HAVING clause goes after the GROUP BY clause (which creates the group) and before the optional ORDER BY clause (which sorts the data).

Basic Having clause syntax

SELECT select_list
FROM table_name
GROUP BY col1, col2, col3 
HAVING group_filter_condition;

Example: This statement returns the sum of the salaries of department_id and department_id only for those departments whose total salary is greater than 40,000.

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id
HAVING SUM(salary) > 40000;

Combination of WHERE and HAVING

➔ WHERE is used to filter individual rows before grouping, while HAVING is used to filter groups after aggregation or grouping.

Example: This SQL query calculates the average salary for a specific job role (SA_REP) within each department, then filters the results to show only those departments where that average salary is greater than 7,000.

SELECT department_id, AVG(salary)
FROM hr.employees
-- WHERE Filters individual employees
WHERE job_id = 'SA_REP'  
GROUP BY department_id
-- HAVING Filters grouped departments
HAVING AVG(salary) > 7000; 

Using Column Aliases in the Having Clause (Oracle 23ai+):

➔ In Oracle Database 23ai and later versions, column aliases can be defined in the SELECT clause and used directly in the HAVING clause.

Example: This query selects the department_id and number of employees of departments that have a total number of employees greater than 5.

SELECT department_id, COUNT(*) AS emp_count
FROM hr.employees
GROUP BY department_id
HAVING emp_count > 5;

Complex conditions on HAVING clause:

➔ Complex conditions are created on the HAVING clause by combining multiple aggregate functions with logical operators such as AND, OR, and NOT.

Complex Having Clause Syntax

SELECT department_id, COUNT(employee_id), AVG(salary)
FROM employees
GROUP BY department_id
HAVING [complex_condition];

Example: Filter out departments that have more than 5 employees and an average salary of more than 40,000.

SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM hr.employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 40000;

Example: Find job positions where the highest salary is at least twice the lowest salary in the same group.Find job positions where the highest salary is at least twice the lowest salary in the same group.

SELECT job_id, MIN(salary), MAX(salary)
FROM hr.employees
GROUP BY job_id
HAVING MAX(salary) >= (2 * MIN(salary));

Example: Filter groups whose total salary expenses do not fall within a certain range.

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id
HAVING SUM(salary) NOT BETWEEN 110000 AND 220000;

Example: Identify departments whose average salary is higher than the overall company average.

SELECT department_id, AVG(salary)
FROM hr.employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);