August 5, 2013

Example of rollup and cube in sql

SQL> SELECT DEPTNO,SUM(SAL)
 2 FROM EMP
 3 GROUP BY ROLLUP(DEPTNO)
 4 /
 DEPTNO SUM(SAL)
---------- ----------
 10 8750
 20 10875
 30 9400
 29025
SQL> ED
Wrote file afiedt.buf
 1 SELECT NVL(TO_CHAR(DEPTNO),'TOTAL'),SUM(SAL)
 2 FROM EMP
 3* GROUP BY ROLLUP(DEPTNO)
SQL> /
NVL(TO_CHAR(DEPTNO),'TOTAL') SUM(SAL)
---------------------------------------- ----------
10 8750
20 10875
30 9400
TOTAL 29025
SQL> ED
Wrote file afiedt.buf
 1 SELECT NVL(TO_CHAR(DEPTNO),'TOTAL'),SUM(SAL)
 2 FROM EMP
 3* GROUP BY CUBE(DEPTNO)
SQL> /
NVL(TO_CHAR(DEPTNO),'TOTAL') SUM(SAL)
---------------------------------------- ----------
TOTAL 29025
10 8750
20 10875
30 9400
SQL> ED
Wrote file afiedt.buf
 1 SELECT NVL(TO_CHAR(DEPTNO),'TOTAL'),JOB,SUM(SAL)
 2 FROM EMP
 3* GROUP BY ROLLUP(DEPTNO,JOB)
SQL> /
NVL(TO_CHAR(DEPTNO),'TOTAL') JOB SUM(SAL)
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
NVL(TO_CHAR(DEPTNO),'TOTAL') JOB SUM(SAL)
---------------------------------------- --------- ----------
30 9400
TOTAL 29025
13 rows selected.
SQL> ED
Wrote file afiedt.buf
 1 SELECT NVL(TO_CHAR(DEPTNO),'TOTAL'),JOB,SUM(SAL)
 2 FROM EMP
 3* GROUP BY CUBE(DEPTNO,JOB)
SQL> /
NVL(TO_CHAR(DEPTNO),'TOTAL') JOB SUM(SAL)
---------------------------------------- --------- ----------
TOTAL 29025
TOTAL CLERK 4150
TOTAL ANALYST 6000
TOTAL MANAGER 8275
TOTAL SALESMAN 5600
TOTAL PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
NVL(TO_CHAR(DEPTNO),'TOTAL') JOB SUM(SAL)
---------------------------------------- --------- ----------
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
18 rows selected.

No comments:

Post a Comment