Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Calculating Running Totals Department wise???

    Hi Friends,

    I need a report query where in i need to display the running totals w.r.t departments.

    Any help would be greatly appreciated!

    Here is the source data along with my query.


    CREATE TABLE EMPP
    (EMPNO INT,
    ENAME VARCHAR(20),
    SAL INT,
    DEPTNO INT
    )

    INSERT INTO EMPP
    SELECT 7840,'MANISH',2000,10
    UNION ALL
    SELECT 7399,'GAYLE',3000,10
    UNION ALL
    SELECT 5389,'SIDHU',7000,20
    UNION ALL
    SELECT 7690,'ARVIND',3000,20
    UNION ALL
    SELECT 9390,'AJAY',8000,10
    UNION ALL
    SELECT 9373,'KALYAN',2000,20
    UNION ALL
    SELECT 6399,'RAMA',5000,30
    UNION ALL
    SELECT 8390,'MAHER',6000,30
    UNION ALL
    SELECT 9300,'RAVI',3000,30

    with cte
    as
    (
    select
    row_number() over(order by deptno) as "seq_no"
    ,row_number() over(partition by deptno order by deptno) as "seq_no_deptno"
    ,empno
    ,ename
    ,deptno
    ,sal
    from empp a
    )
    select a.seq_no
    ,a.seq_no_deptno
    ,a.empno
    ,a.ename
    ,a.deptno
    ,a.sal
    ,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no) as "RunningTotal"
    from cte as a

    -- output
    /* sal runningTotal
    1 1 7840 MANISH 10 2000 2000
    2 2 7399 GAYLE 10 3000 5000
    3 3 9390 AJAY 10 8000 13000
    4 1 9373 KALYAN 20 2000 15000
    5 2 5389 SIDHU 20 7000 22000
    6 3 7690 ARVIND 20 3000 25000
    7 1 6399 RAMA 30 5000 30000
    8 2 8390 MAHER 30 6000 36000
    9 3 9300 RAVI 30 3000 39000
    */

    -- The above is correct but my other requirement is , can i get running the w.r.t. departments

    -- Typically, my output should look like below one : i need add a new column "RunningTotalWRTDept" to the output

    /*
    seq_no seq_no_deptno empno ename deptno sal runningTotal RunningTotalWRTDept
    1 1 7840 MANISH 10 2000 2000 2000
    2 2 7399 GAYLE 10 3000 5000 5000
    3 3 9390 AJAY 10 8000 13000 13000
    4 1 9373 KALYAN 20 2000 15000 2000 -- this is reseted for deptno=20 observe!!!
    5 2 5389 SIDHU 20 7000 22000 9000
    6 3 7690 ARVIND 20 3000 25000 12000
    7 1 6399 RAMA 30 5000 30000 5000 -- this is again resetted for dept=30
    8 2 8390 MAHER 30 6000 36000 11000
    9 3 9300 RAVI 30 3000 39000 14000
    */


    Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Simple change...


    Code:
    with cte
    as
    (
    select
    row_number() over(order by deptno) as "seq_no"
    ,row_number() over(partition by deptno order by deptno) as "seq_no_deptno"
    ,empno
    ,ename
    ,deptno
    ,sal
    from empp a
    )
    select a.seq_no
    ,a.seq_no_deptno
    ,a.empno
    ,a.ename
    ,a.deptno
    ,a.sal
    ,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no) as "RunningTotal"
    ,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no and a.deptno = b.deptno) as "RunningTotalDept"
    from cte as a
    Last edited by EngadaSQL; 06-17-09 at 02:00.

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    Thank You so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •