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;

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;

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;

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;

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.