HAVING CLAUSE

---

Oracle - Having Clause

The HAVING clause is an optional clause of the SELECT statement. It is used to filter groups of rows returned by the GROUP BY clause where condition is TRUE. This is why the HAVING clause is usually used with the GROUP BY clause.

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 "salesinventory"

  
  

  salesinventory table:

  
  

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

  
  

  ITEM	SALE	BILLING_ADDRESS
  Shoes	120	Hyderabad
  Bottles	105	Secunderabad
  Shoes	45	Allahabad
  Bottles	25	Nizampet
  Saree	5000	Cheerala
  Computer	210	Delhi
  Saree	210	Kanchi

  
  

  Execute this query:

  
  

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

  
  

  Output :

  
  

  ITEM	TOTAL SALES
  Shoes	165
  Bottles	130
  Computer	210

  
  

  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,   
      "CITY" VARCHAR2(4000)  
     )  
  /  
  
  

  
  

  NAME	AGE	SALARY	CITY
  Seethu	16	12000	Bangalore
  Nithya	21	25000	Bangalore
  Sanvika	26	56000	Bangalore
  Pranvitha	21	25000	Hyderabad
  Sresta	21	23000	Hyderabad
  Gauri	27	27000	Hyderabad
  Danush	31	31000	Maharashtra
  Pralaynath	65	76000	Kerala
  Pranav	56	87000	Kerala

  
  

  Execute this query:

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

  
  

  Output:

  city	Number of customers
  Bangalore	3
  Hyderabad	3
  Kerala	2

  
  

  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  
     )  
  /  
  

  
  

  EMP_ID	NAME	AGE	DEPARTMENT	SALARY
  1	Soumya	21	Mechanical	12000
  2	Sai charan	22	Software	15000
  3	Sai kiran	23	Mechanical	20000
  4	Keerthana	21	Mechanical	25000
  5	Ajay	24	Software	35000
  6	Sweejya	22	Software	45000
  7	Nainika	26	Software	14000

  
  

  Execute this query:

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

  
  

  Output :

  DEPARTMENT	LOWEST SALARY
  Mechanical	12000
  Software	14000

  
  

  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 :

  Department	HIGHEST SALARY
  Software	35000
  Software	45000

  
  
  

  What Is Oracle

  What Is Oracle

  posted on 2019-11-29 01:11:21 - ORACLE Tutorials

  
  

  TRIGGER

  ORACLE - Trigger

  posted on 2019-11-28 22:14:22 - ORACLE Tutorials

  
  

  CURSOR

  ORACLE - Cursor

  posted on 2019-11-28 22:13:54 - ORACLE Tutorials

  
  

  Prompt Examples

  ChatGPT Prompt Examples

  posted on 2023-06-21 22:37:19 - ChatGPT Tutorials

  
  

  Use Cases

  Chat GPT Key Use Cases

  posted on 2023-06-21 21:03:17 - ChatGPT Tutorials

  
  

  Prompt Frameworks

  Prompt Frameworks

  posted on 2023-06-21 19:33:06 - ChatGPT Tutorials