Group function Enhanced

. Group function Enhanced .

NORMAL GROUP BY

SELECT AVG(SAL),MAX(SAL), COUNT(*) FROM EMP WHERE JOB LIKE 'CL%' GROUP BY SAL;

NORMAL HAVING CLAUSE

SELECT deptNO, AVG(sal) FROM emp GROUP BY deptNO HAVING AVG(sal) >2200;

ROLLUP OPERATOR(DISPLAYS GROUP FUNCTIONS SOLUTION ON PERTICULAR WHERE CLAUSE)

SELECT deptNO, JOB, SUM(sal) FROM emp WHERE deptNO <>

CUBE OPERATOR

SELECT deptNO, JOB, SUM(sal) FROM emp WHERE deptNO <>

GROUPING (USING CUBE)

SELECT deptNO, JOB, SUM(sal),GROUPING(DEPTNO) DEPAT,GROUPING(JOB) JOB FROM emp WHERE deptNO <>

GROUPING OPERATOR(USIING ROLLUP)

SELECT DEPTNO DEPTID, job JOB,SUM(SAL),GROUPING(deptNO) GRP_DEPT,GROUPING(job) GRP_JOB FROM emp WHERE deptNO <>

GROUPING SETS

SELECT DEPTNO,JOB,MGR,AVG(SAL) FROM EMP GROUP BY GROUPING SETS((DEPTNO,JOB),(JOB,MGR));

COMPOSIT COLUMN

SELECT DEPTNO,JOB,MGR,AVG(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,(JOB,MGR));