Having Clause

Having Clause

SQL HAVING Clause:- This statement is used filter the records of grouping functions like min,max,avg,count ..etc .This is used with "Group by" clause for filtering the result of group by query.


Syntax:-

SELECT
    col1, col2, aggregate_function(column_name), ....
    FROM table_name
    [ WHERE condition ]
    GROUP BY col1,col2
    HAVING condition;

Example :- Queries with "Group by" clause

SQL>select job,count(*) from emp group by job;

SQL>select deptno,count(*),min(sal),max(sal) from emp

group by deptno;

SQL>select deptno,job,count(*) from emp
group by deptno,job
order by 1;
 

ROLLUP & CUBE FUNCTION

 

SQL>select job,count(*) from emp
group by ROLLUP(job);

SQL>select job,count(*) from emp
group by cube(job);

SQL>select deptno,job,count(*) from emp
group by rollup(deptno,job)
order by 1;
 

SQL>select deptno,job,count(*) from emp
group by cube(deptno,job)
order by 1;

HAVING Example


SQL>select deptno,job,count(*) from emp
group by deptno,job having count(*)>1
order by 1;

SQL>select deptno,job,count(*) from emp
group by deptno,job having deptno=10
order by 1;
 

SQL>select deptno,job,count(*) from emp where deptno=10
group by deptno,job
order by 1;