Cumulative addition of column values in SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Cumulative addition of column values in SQL

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    Cumulative addition of column values in SQL

    I have the following query...

    SQL> select tkt_no, shift_no, t_date, line_no, prod_time
    2 from mcd_jobcarddirect_dt;

    TKT_NO SH T_DATE LINE_NO PROD_TIME
    --------- -- --------- ---------- ---------
    1 1 20-JUN-03 1 335
    1 1 20-JUN-03 1 335
    1 1 20-JUN-03 1 335

    Now the PROD_TIME field here is displaying actual values. I want here that PROD_TIME field should get added by it's previous value for the group of TKT_NO, SHIFT_NO, T_DATE For e.g. it should look like this


    TKT_NO SH T_DATE LINE_NO PROD_TIME
    --------- -- --------- ---------- ---------
    1 1 20-JUN-03 1 335
    1 1 20-JUN-03 1 670
    1 1 20-JUN-03 1 1005

    How should I do that. I tried to use group by on above 3 fields but SUM wont be useful for cumulative additions.

    Amol

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I dont think u can achive this is single query ( though not sure if any fn )...

    Well ,if you want in single query, then possibly u could have a dummy table or a COL in same table which hold a sequential numbering.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    SELECT tkt_no, shift_no, t_date, line_no,
           SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
                               ORDER BY tkt_no, shift_no, t_date)
    FROM mcd_jobcarddirect_dt;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Code:
    SELECT tkt_no, shift_no, t_date, line_no,
           SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
                               ORDER BY tkt_no, shift_no, t_date)
    FROM mcd_jobcarddirect_dt;
    Are you sure will that fit his requirement?

    PS below, i have some thing like

    Code:
    us18:dba>desc Test_Serial
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------
     ID                                                             NUMBER
     ID_DATE                                                        DATE
     TIME_LAPSED                                                    NUMBER
    
    us18:dba>select * from Test_Serial;
    
                      ID ID_DATE            TIME_LAPSED
    -------------------- --------- --------------------
                       2 20-JUN-03                    1
                       2 20-JUN-03                    1
                       2 20-JUN-03                    1
    
    and i want result set as 
    
                      ID ID_DATE            TIME_LAPSED
    -------------------- --------- --------------------
                       2 20-JUN-03                    1
                       2 20-JUN-03                    2
                       2 20-JUN-03                    3
    I think this is what his Requirement is...

    according to your suggested query, i get

    Code:
    us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,id_date order by Id,id_date)
      2  from Test_Serial;
    
                      ID ID_DATE   SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
    -------------------- --------- ------------------------------------------------------------
                       2 20-JUN-03                                                            1
                       2 20-JUN-03                                                            1
                       2 20-JUN-03                                                            1
    
    us18:dba>
    Am i missing any thing?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Am i missing any thing?
    Your ID_DATE probably contains time fracton too, I suppose. So you actualy have three different dates for id=2. Try using

    ... OVER(PARTITION BY ID,TRUNC(id_date) order by Id,id_date) ...

    instead.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Your ID_DATE probably contains time fracton too, I suppose. So you actualy have three different dates for id=2. Try using

    ... OVER(PARTITION BY ID,TRUNC(id_date) order by Id,id_date) ...

    instead.
    With all rows for col ID_DATE being same i get,

    Code:
    us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,id_date order by Id,id_date)
      2  from Test_Serial;
    
                      ID ID_DATE   SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
    -------------------- --------- ------------------------------------------------------------
                       2 20-JUN-03                                                            3
                       2 20-JUN-03                                                            3
                       2 20-JUN-03                                                            3
    
    Or 
    
    us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,trunc(id_date) order by Id,id_date)
      2  from Test_Serial;
    
                      ID ID_DATE   SUM(TIME_LAPSED)OVER(PARTITIONBYID,TRUNC(ID_DATE)ORDERBYID,ID_DATE)
    -------------------- --------- -------------------------------------------------------------------
                       2 20-JUN-03                                                                   3
                       2 20-JUN-03                                                                   3
                       2 20-JUN-03                                                                   3
    But not

    Code:
                      ID ID_DATE   SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
    -------------------- --------- ------------------------------------------------------------
                       2 20-JUN-03                                                            1
                       2 20-JUN-03                                                            2
                       2 20-JUN-03                                                            3
    As per this requirement, i dont think single query can do any good..
    BTW, did you check the query in ur test db, for such kinda o/p?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Of course it can be in single SQL, jurij forgot windowing clause:
    Code:
    SELECT tkt_no, shift_no, t_date, line_no,
           SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
                               ORDER BY tkt_no, shift_no, t_date
                               rows between unbounded preceding and current row)
    FROM mcd_jobcarddirect_dt;
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    Of course it can be in single SQL, jurij forgot windowing clause:
    Code:
    SELECT tkt_no, shift_no, t_date, line_no,
           SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
                               ORDER BY tkt_no, shift_no, t_date
                               rows between unbounded preceding and current row)
    FROM mcd_jobcarddirect_dt;
    Toma :

    Super..aint knowing about that "unbounded preceding and current row"
    Thanks..

    Where can i find reference?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yep, that was it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Where can i find reference?
    In SQL manual. Windowing clause is (optional) part of any analytical function.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width