Monday, July 5, 2010

OLAP.sql

select deptno,sum(sal) FROM emp GROUP BY rollup(deptno);
select deptno,job,sum(sal) FROM emp GROUP BY rollup(deptno,job);
select deptno,job,sum(sal) FROM emp GROUP BY rollup(job,deptno);
select deptno,comm,sum(comm) FROM emp GROUP BY rollup(comm,deptno);
select deptno,comm,sum(comm) FROM emp GROUP BY rollup(deptno,comm);

select deptno,job,sum(sal) FROM emp GROUP BY cube(job,deptno);
select Grouping(deptno),deptno,job,sum(sal) FROM emp GROUP BY rollup(job,deptno);

SELECT deptno,Decode(deptno,10,'raju',20,'venkat','others') departments from emp;

SELECT deptno,Decode(Grouping(deptno),1,'Sum Of Job Total ') result,job,sum(sal) FROM emp GROUP BY rollup(job,deptno);
SELECT deptno,Decode(Grouping(deptno),1,'All Departments') Depts ,Grouping(deptno) GRD,job,Decode(Grouping(job),1,'All Designations') Jobs,Grouping(job) GRJ,sum(sal) FROM emp GROUP BY cube(job,deptno);

SELECT deptno,job,Sum(sal) FROM emp GROUP BY Grouping sets(job,deptno);

--> CASE
SELECT empno,ename,sal,job,
CASE job
WHEN 'MANAGER' THEN 'MAN'
WHEN 'MANAGER' THEN 'MAN'
WHEN 'MANAGER' THEN 'MAN'
ELSE 'OTHERS' END FROM EMP;

No comments:

Post a Comment