Oracle PL/SQL/Select Query/HAVING

Материал из SQL эксперт
Перейти к: навигация, поиск

Any conditions based on the outcome of a group function must be in the HAVING clause

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> --You can still include a WHERE clause, but any conditions based on the outcome of a group SQL> --function must be placed in the HAVING clause. SQL> SQL> SELECT City, AVG(Salary) AS "Average Salary"

 2  FROM Employee
 3  WHERE City ="Vancouver"
 4  GROUP BY City
 5  HAVING AVG(Salary) > 1000;

CITY Average Salary


--------------

Vancouver 3823.78 SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Born after "1960-01-01", group by department number with count(*) >= 4;

   <source lang="sql">
   

SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal        NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select deptno, count(empno)

 2  from     emp
 3  where    bdate > date "1960-01-01"
 4  group by deptno
 5  having   count(*) >= 4;
   DEPTNO COUNT(EMPNO)

------------
       30            5

SQL> SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


Example using the MAX function with having clause

   <source lang="sql">
  

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SQL> -- Example using the MAX function SQL> SQL> SELECT JOB, MAX(sal) as "Highest salary"

 2      FROM emp
 3      GROUP BY job
 4      HAVING MAX(sal) < 50000;

JOB Highest salary


--------------

CLERK 1300 SALESMAN 1600 PRESIDENT 5000 MANAGER 2975 ANALYST 3000 SQL> SQL> SQL> drop table emp; SQL> SQL> SQL>



 </source>
   
  


Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query

   <source lang="sql">
  

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION


---------- ---------- ----------- ----------- ---------- ---------- ---------------

01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer 02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester 03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester 04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager 05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester 06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester 07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager 08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> --Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query. SQL> SQL> SELECT city, AVG(salary)

 2  FROM employee
 3  GROUP BY city
 4  HAVING AVG(salary) <
 5    (SELECT MAX(AVG(salary))
 6     FROM employee
 7     GROUP BY city);

CITY AVG(SALARY)


-----------

Toronto 1234.56 Vancouver 3823.78 SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Sub query inside having clause

   <source lang="sql">
  

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SELECT count(*), city

 2  FROM employee
 3  GROUP BY city
 4  HAVING count(*) =
 5  (SELECT max(count(*))
 6  FROM employee
 7  GROUP BY city);
 COUNT(*) CITY

----------
        5 Vancouver

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Use sum in having clause

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null); 1 row created. SQL> SQL> SQL> SELECT SUBSTR(product_name, 1, 15) "Product",

 2         SUM(quantity_on_hand) "Total Sold",
 3         AVG(quantity_on_hand) "Average",
 4         COUNT(quantity_on_hand) "Transactions",
 5         MIN(quantity_on_hand) "Fewest",
 6         MAX(quantity_on_hand) "Most"
 7  FROM   product
 8  GROUP BY product_name
 9  HAVING SUM(quantity_on_hand) < 5;

Product Total Sold Average Transactions Fewest Most


---------- ---------- ------------ ---------- ----------

Small Widget 1 1 1 1 1 SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL>


 </source>
   
  


Using and, or operator in having clause

   <source lang="sql">
   

SQL> SQL> CREATE TABLE server_usage (

 2    pro_id                   NUMBER(4),
 3    emp_id                  NUMBER,
 4    time_log_date                DATE,
 5    hours_logged                 NUMBER(8,2),
 6    dollars_charged              NUMBER(8,2),
 7    CONSTRAINT server_usage_pk  PRIMARY KEY (pro_id, emp_id, time_log_date)
 8  );

Table created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);

1 row created. SQL> SQL> SET ECHO ON SQL> SELECT emp_id, pro_id

 2  FROM server_usage
 3  GROUP BY emp_id, pro_id
 4  HAVING (pro_id = 1001 OR pro_id=1002)
 5     AND SUM(hours_logged) > 20;
      101       1001
      102       1002
      106       1001
      107       1002

4 rows selected. SQL> SQL> drop table server_usage; Table dropped.



 </source>
   
  


Using avg() function in having clause

   <source lang="sql">
   

SQL> SQL> create table gift(

 2           gift_id                integer         primary key
 3          ,emp_id                integer
 4          ,register_date              date not null
 5          ,total_price        number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment        varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,message        varchar2(100)
12  );

Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values

 2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values

 2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");

1 row created. SQL> SQL> SQL> select to_char(register_date, "YYYY/MM") "Month",

 2         avg(total_price) "Avg Sales by Month"
 3    from gift
 4   group by to_char(register_date, "YYYY/MM")
 5  having avg(total_price) > 35;

Month Avg Sales by Month


------------------

1999/02 57.51 1 row selected. SQL> SQL> SQL> drop table gift; Table dropped.



 </source>
   
  


Using HAVING with an Analytical Function

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    empno              Number(3)  NOT NULL, -- Employee ID
 3    ename              VARCHAR2(10 BYTE),   -- Employee Name
 4    hireDate          DATE,                -- Date Employee Hired
 5    orig_salary        Number(8,2),         -- Orignal Salary
 6    curr_salary        Number(8,2),         -- Current Salary
 7    region             VARCHAR2(1 BYTE)     -- Region where employeed
 8  )
 9  /

Table created. SQL> SQL> create table job(

 2    empno              Number(3)  NOT NULL, -- Employee ID
 3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
 4  )
 5  /

Table created. SQL> -- prepare data for employee table SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
 3  /

1 row created. SQL> SQL> -- prepare data for job table SQL> SQL> insert into job(empno, jobtitle)

 2           values(101,   "Painter");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(122,   "Tester");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(123,   "Dediator");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(104,   "Chemist");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(105,   "Accountant");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(116,   "Manager");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(117,   "Programmer");

1 row created. SQL> SQL> insert into job(empno, jobtitle)

 2           values(108,   "Developer");

1 row created. SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

Hit a key to continue

    EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R

---------- --------- ----------- ----------- -
      122 Alison     21-MAR-96       45000       48000 E
      123 James      12-DEC-78       23000       32000 W
      104 Celia      24-OCT-82       53000       58000 E
      105 Robert     15-JAN-84       31000       36000 W
      116 Linda      30-JUL-87       43000       53000 E
      117 David      31-DEC-90       78000       85000 W
      108 Jode       17-SEP-96       21000       29000 E

7 rows selected. SQL> select * from job; Hit a key to continue

    EMPNO JOBTITLE

----------
      101 Painter
      122 Tester
      123 Dediator
      104 Chemist
      105 Accountant
      116 Manager
      117 Programmer
      108 Developer

8 rows selected. SQL> SQL> SQL> -- Using HAVING with an Analytical Function SQL> SQL> -- if a HAVING clause is added, it will have its effect just before the RANK. SQL> SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,

 2    MIN(orig_salary) minsalary
 3    --   RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
 4  FROM employee e, job j
 5  WHERE e.orig_salary < 43000
 6    AND e.empno = j.empno
 7  GROUP BY j.jobtitle
 8  HAVING MAX(orig_salary) > 34000
 9  ORDER BY j.jobtitle desc;

no rows selected SQL> SQL> SQL> SQL> SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL> SQL>



 </source>
   
  


Using the HAVING Clause

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> --Using the HAVING Clause SQL> SQL> SQL> --The HAVING clause is essentially a second WHERE clause that is evaluated after SQL> --Oracle has calculated the grouping values. SQL> SQL> SQL> SELECT City, AVG(Salary) AS "Average Salary"

 2  FROM Employee
 3  GROUP BY City
 4  HAVING AVG(Salary) > 500;

CITY Average Salary


--------------

New York 6110.28 Toronto 1234.56 Vancouver 3823.78 SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Using the HAVING Clause and where clause

   <source lang="sql">
   

SQL> SQL> CREATE TABLE SAT (

 2     StudentID  INT NOT NULL,
 3     ExamID     INT NOT NULL,
 4     Mark       INT,
 5     IfPassed   SMALLINT,
 6     Comments   VARCHAR(255),
 7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, AVG(Mark) AS AverageMark

 2  FROM SAT
 3  WHERE ExamID IN ( 5, 8, 11 )
 4  GROUP BY StudentID
 5  HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

no rows selected SQL> SQL> SQL> SQL> SQL> drop table SAT; Table dropped.



 </source>
   
  


Using the HAVING Clause with aggregate function

   <source lang="sql">
   

SQL> SQL> CREATE TABLE SAT (

 2     StudentID  INT NOT NULL,
 3     ExamID     INT NOT NULL,
 4     Mark       INT,
 5     IfPassed   SMALLINT,
 6     Comments   VARCHAR(255),
 7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, AVG(Mark) AS AverageMark

 2  FROM SAT
 3  GROUP BY StudentID
 4  HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

no rows selected SQL> SQL> SQL> SQL> drop table SAT; Table dropped.



 </source>
   
  


Using the same condition in having and where

   <source lang="sql">
   

SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal        NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select deptno, count(*)

 2  from   emp
 3  group  by deptno
 4  having deptno <= 20;
   DEPTNO   COUNT(*)

----------
       10          3
       20          5

SQL> SQL> select deptno, count(*)

 2  from   emp
 3  where  deptno <= 20
 4  group  by deptno;
   DEPTNO   COUNT(*)

----------
       10          3
       20          5

SQL> SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


Using the SUM function in HAVING Clause

   <source lang="sql">
  

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SQL> -- Using the SUM function in HAVING Clause SQL> SQL> SELECT job, SUM(sal) as "Total salary"

 2      FROM emp
 3      GROUP BY job
 4      HAVING SUM(sal) > 1000;

JOB Total salary


------------

CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000 SQL> SQL> SQL> drop table emp; SQL> SQL>



 </source>