Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Getting strange result when using analytic function to calculate running sum.
I use analytic function to calculate running sum, but I found the
result is strange.
SQL> SELECT DEPTNO ,ENAME ,SAL
2 ,SUM(SAL) OVER (
3 PARTITION BY DEPTNO
4 ORDER BY SAL
5 ) AS DEPT_RUNNING_SUM
6 FROM EMP
7 WHERE DEPTNO IN (10, 20)
8 ORDER BY DEPTNO ,SAL
9 ;
DEPTNO ENAME SAL DEPT_RUNNING_SUM --------- -------------------- --------- ---------------- 10 MILLER 1300 1300 10 CLARK 2450 3750 10 KING 5000 8750 20 SMITH 800 800 20 ADAMS 1100 1900 20 JONES 2975 4875 20 SCOTT 3000 10875 20 FORD 3000 10875
Why is SCOTT's DEPT_RUNNING_SUM equal to 10875, not 7875 (4875+3000)? Is it related to windowing or the same salary of FORD and SCOTT?
If I want to show 7875 for SCOTT's salary, how should I write the SQL?
Thanks for your time. Received on Fri Aug 19 2005 - 10:20:15 CDT
![]() |
![]() |