Having clause filters groups of rows

The HAVING clause filters groups of rows. The HAVING clause is after the GROUP BY clause.


SELECT ... 
FROM ... 
WHERE 
GROUP BY ... 
HAVING ... 
ORDER BY ...;

GROUP BY can be used without HAVING, but HAVING must be used in conjunction with GROUP BY.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT deptno, AVG(sal)
  2  FROM emp
  3  GROUP BY deptno
  4  HAVING AVG(sal) > 1500;

    DEPTNO   AVG(SAL)
---------- ----------
        30       1690
        20    2068.75
        10       2925

SQL>

Home »
Oracle »
Select »

Having: