Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

Oracle HAVING


In Oracle, HAVING Clause is used with GROUP BY Clause to restrict the groups of returned rows where condition is TRUE.

Syntax:

  1. SELECT expression1, expression2, … expression_n,
  2.  aggregate_function (aggregate_expression)
  3. FROM tables
  4. WHERE conditions
  5. GROUP BY expression1, expression2, … expression_n
  6. 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:

  1. CREATE TABLE  “SALESDEPARTMENT”
  2.    (    “ITEM” VARCHAR2(4000),
  3.     “SALE” NUMBER,
  4.     “BILLING_ADDRESS” VARCHAR2(4000)
  5.    )
  6. /

groupby1

Execute this query:

  1. SELECT item, SUM(sale) AS “Total sales”
  2. FROM salesdepartment
  3. GROUP BY item
  4. HAVING SUM(sale) < 1000;

Output:

having1

Oracle HAVING Example: (with GROUP BY COUNT function)

Let’s take a table “customers”

Customer table:

  1. CREATE TABLE  “CUSTOMERS”
  2.    (    “NAME” VARCHAR2(4000),
  3.     “AGE” NUMBER,
  4.     “SALARY” NUMBER,
  5.     “STATE” VARCHAR2(4000)
  6.    )
  7. /

groupby3

Execute this query:

  1. SELECT state, COUNT(*) AS “Number of customers”
  2. FROM customers
  3. WHERE salary > 10000
  4. GROUP BY state
  5. HAVING COUNT(*) >= 2;

Output:

having4

Oracle HAVING Example: (with GROUP BY MIN function)

Let’s take a table “employees”

Employees table:

  1. CREATE TABLE  “EMPLOYEES”
  2.    (    “EMP_ID” NUMBER,
  3.     “NAME” VARCHAR2(4000),
  4.     “AGE” NUMBER,
  5.     “DEPARTMENT” VARCHAR2(4000),
  6.     “SALARY” NUMBER
  7.    )
  8. /

groupby5

Execute this query:

  1. SELECT department,
  2. MIN(salary) AS “Lowest salary”
  3. FROM employees
  4. GROUP BY department
  5. HAVING MIN(salary) < 15000;

Output

having6

Oracle HAVING Example: (with GROUP BY MAX function)

Execute this query:

  1. SELECT department,
  2. MAX(salary) AS “Highest salary”
  3. FROM employees
  4. GROUP BY department
  5. HAVING MAX(salary) > 30000;

Output:

having7

Comments

comments