Re: Getting strange result when using analytic function to calculate running sum.

HKLN wrote:

> 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?

The latter.

>
> If I want to show 7875 for SCOTT's salary, how should I write the SQL?
>
> Thanks for your time.
>

Try this for line 4:

ORDER BY SAL, EMPNO -Mark Bole Received on Fri Aug 19 2005 - 13:29:59 CDT