Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Getting strange result when using analytic function to calculate running sum.

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

From: HKLN <hkln_at_hkln.net>
Date: 19 Aug 2005 08:20:15 -0700
Message-ID: <1124464815.641284.167850@g49g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US