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 9400
50 1300
29025

SQL> SELECT DECODE(DEPTNO,…