Reporting Function

[debasisdas]

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.REPOR TING 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.REPORT ING 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,REPOR TING 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;