Oracle Having | Oracle Having Clause - oracle tutorial - sql tutorial



What is Oracle HAVING Clause ?

  • In Oracle, HAVING Clause is used with GROUP BY Clause to restrict the groups of returned rows where condition is TRUE.
  • The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list.
  • If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group.
  • Oracle having group by clause

    Oracle group by - having clause

  • The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Syntax:

SELECT expression1, expression2, ... expression_n, 
 aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING having_condition;
click below button to copy the code. By - oracle tutorial - team

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 "Wikitechy_Salesdepartment"
Wikitechy_Salesdepartment table:
 CREATE TABLE  "Wikitechy_Salesdepartment" 
   (	"ITEM" VARCHAR2(4000), 
	"SALE" NUMBER, 
	"BILLING_ADDRESS" VARCHAR2(4000)
   )
click below button to copy the code. By - oracle tutorial - team

Execute this query:

 SELECT item, SUM(sale) AS "Total sales"
FROM Wikitechy_Salesdepartment
GROUP BY item
HAVING SUM(sale) < 1000;
click below button to copy the code. By - oracle tutorial - team

Oracle HAVING Example: (with GROUP BY COUNT function)

  • Let's take a table "wikitechy_customers"
Wikitechy_Customer table:
 CREATE TABLE  "WIKITECHY_CUSTOMERS" 
   (	"NAME" VARCHAR2(4000), 
	"AGE" NUMBER, 
	"SALARY" NUMBER, 
	"STATE" VARCHAR2(4000)
   )
/
click below button to copy the code. By - oracle tutorial - team

Execute this query:

 SELECT state, COUNT(*) AS "Number of customers"
FROM wikitechy_customers
WHERE salary > 10000
GROUP BY state
HAVING COUNT(*) >= 2;
click below button to copy the code. By - oracle tutorial - team

Oracle HAVING Example: (with GROUP BY MIN function)

  • Let's take a table "wikitechy_employees"

Employees table:

 
CREATE TABLE  "WIKITECHY_EMPLOYEES" 
   (	"EMP_ID" NUMBER, 
	"NAME" VARCHAR2(4000), 
	"AGE" NUMBER, 
	"DEPARTMENT" VARCHAR2(4000), 
	"SALARY" NUMBER
   )
/
click below button to copy the code. By - oracle tutorial - team

Execute this query:

 SELECT department, 
MIN(salary) AS "Lowest salary"
FROM wikitechy_employees
GROUP BY department
HAVING MIN(salary) < 15000;
click below button to copy the code. By - oracle tutorial - team

Execute this query:

SELECT department,
MAX(salary) AS "Highest salary"
FROM wikitechy_employees
GROUP BY department
HAVING MAX(salary) > 30000;
click below button to copy the code. By - oracle tutorial - team

Oracle HAVING Example: (with GROUP BY MAX function)


This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle Having | Oracle HAVING Clause