Having Clause In ORACLE

• This clause is used to provide a condition on group functions, which may filter the data based on the data retrieved at group function.

• You must need group by clause while declaring having by clause.

   Example:=  

       

      1)   Display deptno and total sal of those dept whose total sal is greater  than 9000.

Ans : Select deptno, sum(sal) from emp

        Group by deptno

        Having sum(sal)> 9000;

      2)  Display deptno and number of employees working at each dept, only for those departments where more than 3 employees are working.

       Ans :  Select deptno, count(*) from emp

                Group by deptno

                Having count(*)>3;

 3) Calculate designation wise investments only when overall investments on the designation crosses 5000 excluding all such designation having SALES patterns.

        Ans : Select deptno, avg(sal)

                From emp

                where job like 'SALES%'

                Group by deptno

                Having max(sal)>5000;