Home » Oracle SQL » ORACLE HAVING

ORACLE HAVING

HAVING clause in Oracle is used with GROUP BY function to further narrow down or filter rows in a result group as per our requirement.

HAVING is similar to the WHERE clause except it only works for filtering rows in groups and is used after the GROUP BY clause in a query, while WHERE clause is used for filtering rows in column.

Syntax

The general syntax of the HAVING clause is as follows:

SELECT column_names
FROM table-name
GROUP BY column1
HAVING condition;

Here,

column1 – It is the column we use for grouping data.

condition – Its the condition we give to filter data.

Examples

We will be using the Employee table from HR schema for the following examples:

1) Simple Oracle HAVING Query

The following query returns sum of salary of employees in each department.

SELECT department_id, SUM( salary ) Salary
FROM employees
GROUP BY
department_id
ORDER BY
department_id;

Result:

In the following query, we use the HAVING clause to further filter the rows we got after GROUP BY by giving a condition, where sum of salary is greater than 10000.

SELECT department_id, SUM( salary ) Salary
FROM employees
GROUP BY
department_id
HAVING
SUM( salary ) > 10000
ORDER BY
department_id;

Result:

The query returns only those departments where the sum of salary is greater than 10000.

2. HAVING with complex condition

Suppose, we want to find each department where the employee count is between 2 to 8 and the sum of salary is salary is greater than 20000. We also want to see the name of department in the results.

For this we need to break down the query:

We select all the columns we want to show in the SELECT statement and then we use INNER JOIN to join Departments and Employee table using department_id and then we give two conditions in HAVING clause.

SELECT
    department_name,
    COUNT( employee_id ) employee_count,
    SUM( salary ) total_salary
FROM
    employees
INNER JOIN departments USING (department_id)
GROUP BY
    department_name
HAVING
    SUM( salary ) > 20000 AND
    COUNT( employee_id ) BETWEEN 2 AND 8
ORDER BY
    department_name;

Result:

The query returns the department where the employee count is in the range of 2 to 8 and the sum of salary is greater than 20000. We can also see the department name in the results as we used INNER JOIN.

In this tutorial, we learned how to use HAVING clause to further filter results after using GROUP BY clause.

<< Previous
GROUP BY

Next >>
SELF JOIN