SQL Having

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;

SQL Inline View

Inline View

Inline View :- Query , that is used in place of table name (front of From) is called a Inline-view. This query work as a logical table(view) that's why this is called inline view.

Syntax :- select * from () where ;

In above syntax , is a inline view

Example:-

select * from (select empno,ename,job,sal from emp where sal>2000) ;

Here "select empno,ename,job,sal from emp where sal>2000" is a inline view.


select empno,ename,job,sal,drank from (select empno,ename,job,sal,dense_rank() over(order by sal desc) drank from emp)
where drank<=3;

Note:- Query coloured with blue is inline-view.