...

Database Managment - Oracle

Back to Course

Lesson Description


Lession - #1436 Oracle HAVING Clause


Oracle HAVING Clause

In Oracle, HAVING Clause is utilized with GROUP BY Clause to confine the gatherings of returned lines 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 indicates the articulations that are not exemplified inside total capacity. These articulations should be remembered for GROUP BY proviso.
aggregate_function: It determines the total capacities for example Total, COUNT, MIN, MAX or AVG capacities.

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;


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


Oracle HAVING Example: (with GROUP BY MIN function>


Employees table:

CREATE TABLE  "EMPLOYEES"   
   (    "EMP_ID" NUMBER,   
    "NAME" VARCHAR2(4000>
, "AGE" NUMBER, "DEPARTMENT" VARCHAR2(4000>
, "SALARY" NUMBER >
/


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;