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;
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;
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;
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;
group by deptno,job
order by 1;
No comments:
Post a Comment