1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

sum salary on each row

Discussion in 'SQL PL/SQL' started by mohsin_zee, Mar 8, 2014.

  1. mohsin_zee

    mohsin_zee Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I would like to sum each previous salary to next order by deptno number its just an example but my scenario is same

    Example



    DEPTNO EMPNO SAL TOTAL_SO_FAR
    10 7934 1300
    10 7782 2450 3750
    10 7839 5000 8750
    20 7369 800 800
    20 7876 1100 1900
    20 7566 2975 4875
    20 7788 3000 7875
    20 7902 3000 10875
    30 7900 950 950
    30 7521 1250 2200
    30 7654 1250 3450
    30 7844 1500 4950
    30 7499 1600 6550
    30 7698 2850 9400


    Plz help ......
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    everything is very simple...

    Analytic Function

    additional link : http://www.oracle-base.com/articles/misc/analytic-functions.php


    Code (SQL):

    WITH emp_sal AS
    (
    SELECT 10 DEPTNO , 7934 EMPNO , 1300 SAL  FROM dual UNION ALL  
    SELECT 10 ,7782 ,2450  FROM dual UNION ALL
    SELECT 10, 7839, 5000 FROM dual UNION ALL
    SELECT 20 ,7369, 800  FROM dual UNION ALL
    SELECT 20, 7876, 1100 FROM dual UNION ALL
    SELECT 20, 7566, 2975  FROM dual UNION ALL
    SELECT 20 ,7788 ,3000   FROM dual UNION ALL
    SELECT 20 ,7902, 3000  FROM dual UNION ALL

    SELECT 30 ,7900, 950   FROM dual UNION ALL
    SELECT 30 ,7521 ,1250   FROM dual UNION ALL
    SELECT 30 ,7654 ,1250   FROM dual UNION ALL
    SELECT 30 ,7844 ,1500   FROM dual UNION ALL
    SELECT 30 ,7499 ,1600   FROM dual UNION ALL
    SELECT 30 ,7698 ,2850   FROM dual
    )


    SELECT
    e.*,
    SUM(e.sal) OVER (partition BY e.deptno ORDER BY e.sal,e.empno) total
    FROM emp_sal e



    SQL >

                  DEPTNO      EMPNO        SAL      TOTAL
    ---------- ---------- ---------- ----------
            10       7934       1300       1300
            10       7782       2450       3750
            10       7839       5000       8750
            20       7369        800        800
            20       7876       1100       1900
            20       7566       2975       4875
            20       7788       3000       7875
            20       7902       3000      10875
            30       7900        950        950
            30       7521       1250       2200
            30       7654       1250       3450
            30       7844       1500       4950
            30       7499       1600       6550
            30       7698       2850       9400

     14 ROWS selected

     
     
  3. mohsin_zee

    mohsin_zee Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thanks you make life easy :)