Oracle HAVING Clause

In Oracle, HAVING Clause is used with GROUP BY Clause to restrict the groups of returned rows where condition is TRUE.

Syntax:

SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING having_condition;

Parameters:

expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.

having_conditions: It specifies the conditions that are applied only to the aggregated results to restrict the groups of returned rows.

Oracle HAVING Example: (with GROUP BY SUM function)

Let's take a table "salesdepartment"

Salesdepartment table:

CREATE TABLE "SALESDEPARTMENT"
( "ITEM" VARCHAR2(4000),
"SALE" NUMBER,
"BILLING_ADDRESS" VARCHAR2(4000)
)
/

oracle having example 1

Execute this query:

SELECT item, SUM(sale) AS "Total sales"
FROM salesdepartment
GROUP BY item
HAVING SUM(sale) < 1000;

Output:

oracle having example 2

Oracle HAVING Example: (with GROUP BY COUNT function)

Let's take a table "customers"

Customer table:

CREATE TABLE "CUSTOMERS"
( "NAME" VARCHAR2(4000),
"AGE" NUMBER,
"SALARY" NUMBER,
"STATE" VARCHAR2(4000)
)
/

oracle having example 3

Execute this query:

SELECT state, COUNT(*) AS "Number of customers"
FROM customers
WHERE salary > 10000
GROUP BY state
HAVING COUNT(*) >= 2;

Output:

oracle having example 4

Oracle HAVING Example: (with GROUP BY MIN function)

Let's take a table "employees"

Employees table:

CREATE TABLE "EMPLOYEES"
( "EMP_ID" NUMBER,
"NAME" VARCHAR2(4000),
"AGE" NUMBER,
"DEPARTMENT" VARCHAR2(4000),
"SALARY" NUMBER
)
/

oracle having example 5

Execute this query:

SELECT department,
MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) < 15000;

Output

oracle having example 6

Oracle HAVING Example: (with GROUP BY MAX function)

Execute this query:

SELECT department,
MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) > 30000;

Output:

oracle having example 7

DATABASE

SQL

VIDEOS

Orcale SQL Tutorial

Having Clause

Oracle HAVING Clause

Parameters:

Oracle HAVING Example: (with GROUP BY MIN function)