Using HAVING Clause in Oracle

This article is written about how to use the Oracle HAVING clause with syntax and examples.

Description

The Oracle HAVING clause is used in aggregate with the GROUP BY clause to avoid the businesses of lower back rows to solely these whose the condition is TRUE.

Syntax

The syntax for the HAVING clause in Oracle/PLSQL is:

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

Parameters or Arguments

expression1, expression2, … expression_n The expressions that are now not encapsulated inside an mixture characteristic and should be blanketed in the GROUP BY clause. aggregate_function It can be a characteristic such as SUM, COUNT, MIN, MAX, or AVG functions. aggregate_expression This is the column or expression that the aggregate_function will be used against. tables The tables that you want to retrieve information from. There ought to be at least one table listed in the FROM clause. WHERE conditions Optional. These are the conditions for the records to be selected. having_condition This is a in addition condition applied solely to the aggregated outcomes to hinder the companies of lower back rows. Only those corporations whose situation evaluates to TRUE will be included in the result set.

Example – Using SUM function

Let’s seem to be at an Oracle HAVING clause example that uses the SUM function.

You should additionally use the SUM feature to return the title of the department and the whole income (in the related department). The Oracle HAVING clause will filter the effects so that solely departments with sales higher than $25,000 will be returned.

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 25000;

Example – Using COUNT function

Let’s appear at how we should use the HAVING clause with the COUNT function.

You should use the COUNT function to return the title of the branch and the range of employees (in the related department) that make under $49,500 / year. The Oracle HAVING clause will filter the effects so that solely departments with greater than 10 employees will be returned.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary < 49500
GROUP BY department
HAVING COUNT(*) > 10;

Example – Using MIN function

Let’s subsequent seem to be at how we ought to use the HAVING clause with the MIN function.

You should additionally use the MIN function to return the name of every branch and the minimal revenue in the department. The Oracle HAVING clause will return solely those departments the place the minimal income is much less than $42,000.

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

Example – Using MAX function

MAX function

For example, you may want to also use the MAX function to return the name of each department and the maximum income in the department. The Oracle HAVING clause will return only these departments whose most income is greater than $45,000.

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