469,082 Members | 1,074 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Reporting Function

debasisdas
8,127 Expert 4TB
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
-------------------------
Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,SUM(SAL) "SALARY",SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
  2. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "TOTALSALARY" 
  3. FROM EMP 
  4. GROUP BY HIREDATE 
  5. ORDER BY HIREDATE;
Sample Ex#2
-------------------------
Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,
  2. SUM(SAL) "SALARY",
  3. SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
  4. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "WINDOWSALARY" ,
  5. SUM(SUM(SAL)) OVER() "REPORTINGSALES"
  6. 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
==================

Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,SUM(SAL) "SALARY",
  2. SUM(SUM(SAL)) OVER() "REPORTINGSALES"
  3. FROM EMP
  4. WHERE JOB='MANAGER'
  5. 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
==================

Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,EMPNO,SUM(SAL)"SALARY",SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) "DEPTSAL"
  2. FROM EMP
  3. GROUP BY DEPTNO,EMPNO
  4. ORDER BY DEPTNO,EMPNO
  5.  
RATIO_TO_REPORT
---------------------------------------
Sample Ex#5
=============
Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,
  2.  EMPNO,
  3.  SUM(SAL)"SALARY",
  4.  ROUND(SUM(SAL)/SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTPERCENT"
  5.  FROM EMP
  6.  GROUP BY DEPTNO,EMPNO
  7.  ORDER BY DEPTNO,EMPNO;

Sample Ex#6
=============
Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,
  2. EMPNO,
  3. SUM(SAL)"SALARY",
  4. ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTRATIO"
  5. FROM EMP
  6. GROUP BY DEPTNO,EMPNO
  7. ORDER BY DEPTNO,EMPNO;
Jul 13 '07 #1
0 4285

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by steve | last post: by
8 posts views Thread by Woody Splawn | last post: by
8 posts views Thread by Paul Furman | last post: by
reply views Thread by Dan.Diasio | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.