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 a column that is to be created? (lag)

Discussion in 'SQL PL/SQL' started by jedwards, Feb 7, 2011.

  1. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I have a table and I’m trying to calculate the ttl members by day. See an example table below. The ttl_members is the column I am trying to calculate.

    change_in_members, ttl_members
    22, 29
    -1, 7
    3, 8
    5, 5

    Here’s my attempt

    “change_in_members + lag(ttl_members, 1, 0) OVER (ORDER BY dt) AS ttl_members”

    However, since I am calculating on a column that is not yet created, I get the error ORA-00904: “TTL_MEMBERS”: invalid identifier.

    Any ideas how to do this?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SQL> SELECT CHANGE_IN_MEMBERS CN FROM TT_NUM;

            CN
    ----------
            22
            -1
             3
             5

    SQL> SELECT CHANGE_IN_MEMBERS CN,
           LAG( CHANGE_IN_MEMBERS,1) OVER (ORDER BY ROWNUM) LG,
           SUM(CHANGE_IN_MEMBERS) OVER (ORDER BY NULL) SM  2    3
      4  FROM TT_NUM;

            CN         LG         SM
    ---------- ---------- ----------
            22                    29
            -1         22         29
             3         -1         29
             5          3         29

    SQL> SELECT CN ,
           SM ,
           NVL(SUM(LG) OVER (ORDER BY ROWNUM),0) ttl_members
    FROM (
    SELECT CHANGE_IN_MEMBERS CN,
           LAG( CHANGE_IN_MEMBERS,1) OVER (ORDER BY ROWNUM) LG,
           SUM(CHANGE_IN_MEMBERS) OVER (ORDER BY NULL) SM
    FROM TT_NUM  )  2    3    4    5    6    7    8  ;

            CN         SM TTL_MEMBERS
    ---------- ---------- -----------
            22         29           0
            -1         29          22
             3         29          21
             5         29          24

    SQL> SELECT CN ,
           SM -NVL(SUM(LG) OVER (ORDER BY ROWNUM),0) ttl_members
    FROM (
    SELECT CHANGE_IN_MEMBERS CN,
           LAG( CHANGE_IN_MEMBERS,1) OVER (ORDER BY ROWNUM) LG,
           SUM(CHANGE_IN_MEMBERS) OVER (ORDER BY NULL) SM
    FROM TT_NUM  )  2    3    4    5    6    7  ;

            CN TTL_MEMBERS
    ---------- -----------
            22          29
            -1           7
             3           8
             5           5

    SQL>
     
     
  3. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Raj, thank you very much I appreciate you breaking this up for me! I may have thrown you off by throwing LAG in the title. It seems that
    Code (SQL):
    NVL(SUM(change_in_members) OVER(ORDER BY dt), 0) AS ttl_members
    also works. Thank you again!

    dt could also be rownum I'm assuming
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No.

    Your query will be wrong as far the OP is concerned.

    See the difference.

    Code (SQL):

    SQL> SELECT CN ,
           SM -NVL(SUM(LG) OVER (ORDER BY ROWNUM),0) ttl_members
    FROM (
    SELECT CHANGE_IN_MEMBERS CN,
           LAG( CHANGE_IN_MEMBERS,1) OVER (ORDER BY ROWNUM) LG,
           SUM(CHANGE_IN_MEMBERS) OVER (ORDER BY NULL) SM
    FROM TT_NUM  )  2    3    4    5    6    7  ;

            CN TTL_MEMBERS
    ---------- -----------
            22          29
            -1           7
             3           8
             5           5

    SQL> SELECT CHANGE_IN_MEMBERS,NVL(SUM(CHANGE_IN_MEMBERS) OVER(ORDER BY rownum), 0) FROM  TT_NUM;

    CHANGE_IN_MEMBERS NVL(SUM(CHANGE_IN_MEMBERS)OVER(ORDERBYROWNUM),0)
    ----------------- ------------------------------------------------
                   22                                               22
                   -1                                               21
                    3                                               24
                    5                                               29

    SQL>
    SQL>