This article is written about how to use the Oracle HAVING clause with syntax and examples.
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.
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
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;