ORACLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

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

    ITEMSALEBILLING_ADDRESS
    Shoes120Hyderabad
    Bottles105Secunderabad
    Shoes45Allahabad
    Bottles25Nizampet
    Saree5000Cheerala
    Computer210Delhi
    Saree210Kanchi

    Execute this query:

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

    Output :

    ITEMTOTAL SALES
    Shoes165
    Bottles130
    Computer210

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

    NAMEAGESALARYCITY
    Seethu1612000Bangalore
    Nithya21 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:
    cityNumber of customers
    Bangalore3
    Hyderabad3
    Kerala2

    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_IDNAMEAGEDEPARTMENTSALARY
    1Soumya21Mechanical12000
    2Sai charan22Software15000
    3Sai kiran23Mechanical20000
    4Keerthana21Mechanical25000
    5Ajay24Software35000
    6Sweejya22Software45000
    7Nainika26Software14000

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

    Output :
    DEPARTMENTLOWEST SALARY
    Mechanical12000
    Software14000

    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 :
    DepartmentHIGHEST SALARY
    Software35000
    Software45000


    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


    MySQL TRIGGERS

    MySQL - Triggers

    posted on 2019-11-29 21:44:07 - mysql Tutorials


    Grant_ Revoke Privilege

    MySQL - Grant_ Revoke Privilege

    posted on 2019-11-26 23:15:04 - mysql Tutorials


    MySQL Vs SQL

    MySQL Vs SQL

    posted on 2019-11-25 05:02:26 - mysql Tutorials