Cube with Group By

Cube with Group By :-

Row Labels	Sum of SAL
ANALYST	6000	Cube
20	6000
CLERK	2850	Cube
20	1900
30	950
MANAGER	8275	Cube
10	2450
20	2975
30	2850
PRESIDENT	5000	Cube
10	5000
SALESMAN	5600	Cube
30	5600
Grand Total	27725	Cube
10	7450	Cube
20	10875	Cube
30	9400	Cube

--====================================================

select * from XX_EMP_TL

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20

--====================================================

select  DEPTNO, SUM(SAL) Salary

from XX_EMP_TL

GROUP BY  DEPTNO

DEPTNO	SALARY
30	9400
20	10875
10	7450

select NVL(TO_CHAR(DEPTNO),'TOTAL') DEPTNO, SUM(SAL) SAL

from XX_EMP_TL

GROUP BY CUBE(DEPTNO)

ORDER by 1

DEPTNO	SAL
10	7450
20	10875
30	9400
Total	27725

--===============with 2 Columns ==========================

select JOB, DEPTNO, SUM(SAL) Salary

from XX_EMP_TL

GROUP BY JOB, DEPTNO

JOB	DEPTNO	SALARY
MANAGER	20	2975
PRESIDENT	10	5000
SALESMAN	30	5600
ANALYST	20	6000
MANAGER	30	2850
MANAGER	10	2450
CLERK	30	950
CLERK	20	1900

select NVL(JOB,'TOTAL') JOB, NVL(TO_CHAR(DEPTNO),'TOTAL') DEPTNO, SUM(SAL) SAL from XX_EMP_TL

GROUP BY CUBE(JOB, DEPTNO)

JOB	DEPTNO	SAL
ANALYST	TOTAL	6000
ANALYST	20	6000
CLERK	TOTAL	2850
CLERK	30	950
CLERK	20	1900
MANAGER	30	2850
MANAGER	20	2975
MANAGER	TOTAL	8275
MANAGER	10	2450
PRESIDENT	TOTAL	5000
PRESIDENT	10	5000
SALESMAN	TOTAL	5600
SALESMAN	30	5600
TOTAL	10	7450
TOTAL	TOTAL	27725
TOTAL	20	10875
TOTAL	30	9400

-------  CUBE --------------

select * from (

select --NVL(TO_CHAR(MGR),'TOTAL') MGR,

NVL(JOB,'TOTAL') JOB, NVL(TO_CHAR(DEPTNO),'TOTAL') DEPTNO, SUM(SAL) SAL from XX_EMP_TL

GROUP BY CUBE(JOB, DEPTNO)

) 

PIVOT (SUM(SAL) AS SALARY FOR (DEPTNO) IN (  

  '10' AS "10"     

, '20' AS "20"

, '30' AS "30"

, 'TOTAL' TOTAL

 )  )

 ORDER BY 1

JOB	10_SALARY	20_SALARY	30_SALARY	TOTAL_SALARY
ANALYST		6000		6000
CLERK		1900	950	2850
MANAGER	2450	2975	2850	8275
PRESIDENT	5000			5000
SALESMAN			5600	5600
TOTAL	7450	10875	9400	27725