REPORTING FUNCTIONS ALLOW THE EXECUTION OF VARIOUS AGGREGATE FUNCTIONS AGAINST A RESULT SET.UNLIKE WINDOWING FUNCTIONS THESE CAN'T SPECIFY LOCALISED WINDOWS AND THUS GENERATE THE SAME RESULT FOR EACH ENTIRE PARTITION.REPORTING FUNCTION GENERATES SAME RESULT AS A WINDOWING FUNCTION WITH AN UNBOUNDED WINDOW.
Sample Ex#1
-------------------------
- SELECT HIREDATE,SUM(SAL) "SALARY",SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
-
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "TOTALSALARY"
-
FROM EMP
-
GROUP BY HIREDATE
-
ORDER BY HIREDATE;
Sample Ex#2
-------------------------
- SELECT HIREDATE,
-
SUM(SAL) "SALARY",
-
SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
-
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "WINDOWSALARY" ,
-
SUM(SUM(SAL)) OVER() "REPORTINGSALES"
-
FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
THE EMPTY PARENTHESES AFTER THE OVER CLAUSE FOR THE COLUMN INDICATES THAT THE ENTIRE RESULTSET SHOULD BE INCLUDED IN THE SUM,WHICH HAS SAME EFFECT AS USING AN UNBOUNDED WINDOW FUNCTION.REPORTING FUNCTIONS ARE USEFUL WHEN BOTH DETAIL AND AGGREGATE DATA IS REQUIRED.
Sample Example #3
==================
- SELECT HIREDATE,SUM(SAL) "SALARY",
-
SUM(SUM(SAL)) OVER() "REPORTINGSALES"
-
FROM EMP
-
WHERE JOB='MANAGER'
-
GROUP BY HIREDATE ORDER BY HIREDATE;
LIKE RANKING FUNCTIONS,REPORTING FUNCTIONS CAN ALSO INCLUDE PARTITION BY CLAUSE TO SPLIT THE RESULT SET INTO MULTIPLE PIECES,ALLOWING MULTIPLE AGGREGATIONS TO BE COMPUTED ACCROSS DIFFERENT SUBSETS OF THE RESULT SET.
Sample Example #4
==================
- SELECT DEPTNO,EMPNO,SUM(SAL)"SALARY",SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) "DEPTSAL"
-
FROM EMP
-
GROUP BY DEPTNO,EMPNO
-
ORDER BY DEPTNO,EMPNO
-
RATIO_TO_REPORT
---------------------------------------
Sample Ex#5
=============
-
SELECT DEPTNO,
-
EMPNO,
-
SUM(SAL)"SALARY",
-
ROUND(SUM(SAL)/SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTPERCENT"
-
FROM EMP
-
GROUP BY DEPTNO,EMPNO
-
ORDER BY DEPTNO,EMPNO;
Sample Ex#6
=============
-
SELECT DEPTNO,
-
EMPNO,
-
SUM(SAL)"SALARY",
-
ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTRATIO"
-
FROM EMP
-
GROUP BY DEPTNO,EMPNO
-
ORDER BY DEPTNO,EMPNO;
