Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: help with update query

    Hi Guys,
    i need to update a column with the adding the value above.

    i have a date column and column which needs to be added.

    Sample data :

    Code:
    9/8/2008     1
    9/15/2008             5
    9/22/2008              8
    9/29/2008             8
    10/6/2008             10 
    10/13/2008            85
    10/20/2008            1
    The output has to be
    Code:
    9/8/2008      1
    9/15/2008              6
    9/22/2008              14
    9/29/2008              22
    10/6/2008               32
    10/13/2008              117
    10/20/2008               118
    The idea is to add the values till that date.

    Can you please help me with it.

    Regards,
    Magesh

  2. #2
    Join Date
    Jul 2009
    Posts
    150

    Question Maybe

    You can try following query:

    Code:
    update tbl1 a
    set a.clm2 = (sum (b.clm2) from tbl1 b where b.clm1 <= a.clm1)
    If you don't need to update, just select:

    Code:
    select a.clm1 sum (a.clm2) 
    from tbl1 a, tbl1 b
    where a.clm1 >= b.clm1
    group by a.clm1 
    order by a.clm1
    Kara

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    HI Kara,

    Thanks for your help.. But i am not able to get the desired output.
    The select statement is giving wrong output. Can you please check it..

    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The select statement is giving wrong output. Can you please check it..
    Try:
    Code:
    select a.clm1 , sum (b.clm2) 
    from tbl1 a, tbl1 b
    where a.clm1 >= b.clm1
    group by a.clm1 
    order by a.clm1
    Last edited by tonkuma; 06-01-10 at 12:05. Reason: Add comma(",") between a.colm1 and sum (b.clm2) in the SELECT list

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    thanks tokuma,

    But i am getting 5 records for each of the rows.. i am trying to write a recursive query to achive it.. but am not getting anywhere..

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But i am getting 5 records for each of the rows..
    ???

    Here is my test result.
    (using different table name and column names)
    Test Data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
      FROM update_test;
    ------------------------------------------------------------------------------
    
    DATE_      VALUE_     
    ---------- -----------
    2008-09-08           1
    2008-09-15           5
    2008-09-22           8
    2008-09-29           8
    2008-10-06          10
    2008-10-13          85
    2008-10-20           1
    
      7 record(s) selected.

    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT a.date_
         , SUM(b.value_) AS added_values
      FROM update_test a
         , update_test b
     WHERE a.date_ >= b.date_
     GROUP BY a.date_
     ORDER BY a.date_
    ;
    ------------------------------------------------------------------------------
    
    DATE_      ADDED_VALUES
    ---------- ------------
    2008-09-08            1
    2008-09-15            6
    2008-09-22           14
    2008-09-29           22
    2008-10-06           32
    2008-10-13          117
    2008-10-20          118
    
      7 record(s) selected.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Yes, tonkuma, it works in a right way !

    Lenny

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Is it me? When I learned SQL I was thought not the use the "where" clause in a "group by" query. Use "having" instead.
    And now I see the SQL-ninja (a.k.a Tonkuma) code the "where" clause. Can someone help me with this riddle?

  9. #9
    Join Date
    Jul 2009
    Posts
    58
    Guys,
    i think i am missing something here.

    This is the table on which i want to run the query.

    101 0 2008-09-08 6586.800000
    101 0 2008-09-15 13173.600000
    101 0 2008-09-22 19760.400000
    101 0 2008-09-29 26347.200000
    101 0 2008-10-06 32934.000000
    101 0 2008-10-13 39520.800000
    101 0 2008-10-20 46107.600000
    101 0 2008-10-27 52694.400000
    101 0 2008-11-03 59281.200000
    101 0 2008-11-10 65868.000000
    101 0 2008-11-17 68934.800000
    101 0 2008-11-24 75201.600000
    101 0 2008-12-01 81468.400000
    101 0 2008-12-08 87735.200000
    101 0 2008-12-15 94002.000000
    101 0 2008-12-22 100268.800000
    101 0 2008-12-29 106535.600000
    101 0 2009-01-05 112802.400000
    101 0 2009-01-12 119069.200000
    101 0 2009-01-19 125336.000000
    101 0 2009-01-26 131602.800000
    101 0 2009-02-02 137869.600000
    101 0 2009-02-09 111936.400000
    101 0 2009-02-16 116803.200000
    101 0 2009-02-23 121670.000000
    101 0 2009-03-02 126536.800000
    101 0 2009-03-09 109803.600000
    101 0 2009-03-16 113870.400000
    101 0 2009-03-23 117937.200000
    101 0 2009-03-30 122004.000000
    101 0 2009-04-06 126070.800000
    101 0 2009-04-13 130137.600000
    101 0 2009-04-20 134204.400000
    101 0 2009-04-27 138271.200000
    101 0 2009-05-04 145901.800000
    101 0 2009-05-11 155565.800000
    101 0 2009-05-18 164983.300000
    101 0 2009-05-25 174629.900000
    101 0 2009-06-01 161947.100000
    101 0 2009-06-08 170212.500000
    101 0 2009-06-15 178477.900000
    101 0 2009-06-22 186743.300000
    101 0 2009-06-29 195008.700000
    101 0 2009-07-06 187224.100000
    101 0 2009-07-13 194889.500000
    101 0 2009-07-20 202054.500000
    101 0 2009-07-27 209698.300000
    101 0 2009-08-03 201229.100000
    101 0 2009-08-10 195636.200000
    101 0 2009-08-17 202355.600000
    101 0 2009-08-24 209075.000000
    101 0 2009-08-31 201880.500000
    101 0 2009-09-07 208199.500000
    101 0 2009-09-14 214518.500000
    101 0 2009-09-21 220837.500000
    101 0 2009-09-28 227156.500000
    101 0 2009-10-05 233475.500000
    101 0 2009-10-12 174344.500000
    101 0 2009-10-19 178913.500000
    101 0 2009-10-26 114532.500000
    101 0 2009-11-02 117351.500000
    101 0 2009-11-09 120170.500000
    101 0 2009-11-16 122989.500000
    101 0 2009-11-23 125808.500000
    101 0 2009-11-30 128627.500000
    101 0 2009-12-07 131446.500000
    101 0 2009-12-14 133338.000000
    101 0 2009-12-21 136136.000000
    101 0 2009-12-28 138934.000000
    The query i ran

    SELECT a.D_WEEK
    , SUM(b.Q_PLAND_EVENTS_WEEK) AS added_values
    FROM prg.PRG_BATCH_PLAND_TOTAL a
    , prg.PRG_BATCH_PLAND_TOTAL b
    WHERE a.D_WEEK >= b.D_WEEK and a.I_PGM=101 and a.i_montr=0 and b.I_PGM=101 and b.i_montr=0
    GROUP BY a.D_WEEK
    ORDER BY a.D_WEEK
    This got me the correct result.. i missed the b.i_montr=0 and b.i_pgm=0..

    Thanks for your help guys...

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Is it me? When I learned SQL I was thought not the use the "where" clause in a "group by" query. Use "having" instead.
    And now I see the SQL-ninja (a.k.a Tonkuma) code the "where" clause. Can someone help me with this riddle?
    dr_te_z

    The Where clause and Having clause both filter the results but are applied at different parts of the process.

    The Where clause filters rows as they are being retrieved form the table(s).
    The Having clause filters the rows after the Group By clause has been processed.

  11. #11
    Join Date
    Jul 2009
    Posts
    150

    Talking

    Quote Originally Posted by dr_te_z View Post
    Is it me? When I learned SQL I was thought not the use the "where" clause in a "group by" query. Use "having" instead.
    And now I see the SQL-ninja (a.k.a Tonkuma) code the "where" clause. Can someone help me with this riddle?
    You have to use sales "buy 1 get 1 free", only !

  12. #12
    Join Date
    Jul 2009
    Posts
    58
    Hi all,

    Sorry to bug you again..

    The sql
    SELECT a.date_
    , SUM(b.value_) AS added_values
    FROM update_test a
    , update_test b
    WHERE a.date_ >= b.date_
    GROUP BY a.date_
    ORDER BY a.date_;
    Can anyone help me understand the sql.. I am not able to understand that

  13. #13
    Join Date
    Jul 2009
    Posts
    150

    Question

    Quote Originally Posted by mac4rfree View Post
    Hi all,

    Sorry to bug you again..

    The sql

    Can anyone help me understand the sql.. I am not able to understand that
    Explain to me what you didn't understand.
    I can't understand why that query is not easy to understand.

    Kara

  14. #14
    Join Date
    Jul 2009
    Posts
    58
    actually let me tell you what i did not understand..

    If the data is
    9/8/2008 1
    9/15/2008 5
    9/22/2008 8
    9/29/2008 8
    10/6/2008 10
    10/13/2008 85
    10/20/2008 1
    Then all date other than 9/8/2008 will be greater, in that scenario, the query will add all the records which it should not.
    In similar fashion, 9/15/2008 will be lesser than all the below rows, and their value will be added up. But it should not be..

    Please let me know where i am going wrong..

  15. #15
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb

    Quote Originally Posted by mac4rfree View Post
    actually let me tell you what i did not understand..

    If the data is

    Then all date other than 9/8/2008 will be greater, in that scenario, the query will add all the records which it should not.
    In similar fashion, 9/15/2008 will be lesser than all the below rows, and their value will be added up. But it should not be..

    Please let me know where i am going wrong..
    You have to use dates in DB2 format: yyyy-mm-dd. That it !

    Kara

Posting Permissions

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