Saturday, 24 February 2018

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;

2 comments:


  1. Fantastic article. Thanks to the author. I learnt many things from your blog. Thank you.

    I referred your blog and also the below website

    https://www.wikitechy.com/tutorials/oracle/

    Thanks to you both for helping me.

    ReplyDelete
  2. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...