using aggragrate functions for reporting

Advanced Grouping in oracle, more helpful for report generate.

SQL> SELECT * FROM EMP;

-- GROUP BY CLAUSE

SQL> SELECT deptno, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY deptno;

-- ROLLUP

SQL> SELECT deptno, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal)
FROM emp WHERE sal <= 4000;

SQL> SELECT deptno, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY ROLLUP(deptno);

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY deptno, job;

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY ROLLUP(deptno, job);

-- CUBE

SQL> SELECT deptno, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY CUBE(deptno);

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY CUBE(deptno, job);

-- GROUPING

SQL> SELECT deptno, job, SUM(sal), GROUPING(DEPTNO) G_DNO, GROUPING(JOB) G_JOB
FROM emp WHERE sal <= 4000
GROUP BY ROLLUP(deptno, job);

SQL> SELECT deptno, job, SUM(sal), GROUPING(DEPTNO) G_DNO, GROUPING(JOB) G_JOB
FROM emp WHERE sal <= 4000
GROUP BY CUBE(deptno, job);

-- GROUPING SETS

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY ROLLUP(deptno, job);

SQL> SELECT deptno, JOB, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY GROUPING SETS((DEPTNO), (DEPTNO, JOB), ());

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY CUBE(deptno, job);

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY GROUPING SETS((DEPTNO), (JOB), (DEPTNO, JOB), ());

SQL> SELECT deptno, job, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY GROUPING SETS((DEPTNO), (JOB), ());

SQL> SELECT TO_CHAR(deptno), SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY DEPTNO
UNION ALL
SELECT JOB, SUM(sal)
FROM emp WHERE sal <= 4000
GROUP BY JOB
UNION ALL
SELECT NULL, SUM(sal)
FROM emp WHERE sal <= 4000;