Skip to main content

Posts

Showing posts from April, 2011

ROLLUP IN ORACLE

ROLLUP ======= Extension of Group By Clause, which Provide super aggregate rows. Ex:- ----- Query for sum of salaries by department and also display the total salary of all the departments. Using Set Operator ------------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP GROUP BY DEPTNO 4 UNION 5 SELECT NULL,SUM(SAL) 6 FROM 7 EMP; DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30 9400 50 1300 29025 Using Inline Views -------------------- SQL> SELECT DEPTNO,SUM(SAL) DEPTSAL,A.TOTALSAL TOTALSALARY 2 FROM 3 EMP,(SELECT SUM(SAL) TOTALSAL 4 FROM 5 EMP) A 6 GROUP BY DEPTNO,A.TOTALSAL; DEPTNO DEPTSAL TOTALSALARY ---------- ---------- ----------- 10 7450 29025 20 10875 30 9400 50 1300 Using Rollup ------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP 4 GROUP BY ROLLUP(DEPTNO); DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30