Oracle HAVING Clause
Published by : Obay Salah , December 1, 2024
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) ) /
Execute this query:
SELECT item, SUM(sale) AS "Total sales" FROM salesdepartment GROUP BY item HAVING SUM(sale) < 1000;
Output:
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) ) /
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: (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 ) /
Execute this query:
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 15000;
Output
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:
Comments
no comment yet!