help with update query

mac4rfree · #1 (**permalink**) 05-31-10, 05:36

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

DB2Plus · #2 (**permalink**) 05-31-10, 06:43

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

mac4rfree · #3 (**permalink**) 06-01-10, 10:11

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.

tonkuma · #4 (**permalink**) 06-01-10, 10:21

Quote:

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

mac4rfree · #5 (**permalink**) 06-01-10, 10:32

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..

tonkuma · #6 (**permalink**) 06-01-10, 11:10

Quote:

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.

Lenny77 · #7 (**permalink**) 06-01-10, 11:57

Yes, tonkuma, it works in a right way !

Lenny

dr_te_z · #8 (**permalink**) 06-02-10, 02:55

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?

mac4rfree · #9 (**permalink**) 06-02-10, 05:29

Guys,
i think i am missing something here.

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

Quote:

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

Quote:

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...

Stealth_DBA · #10 (**permalink**) 06-02-10, 07:15

Quote:

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.

DB2Plus · #11 (**permalink**) 06-02-10, 07:20

Quote:

Originally Posted by dr_te_z

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 !

mac4rfree · #12 (**permalink**) 06-02-10, 07:32

Hi all,

Sorry to bug you again..

The sql

Quote:

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

DB2Plus · #13 (**permalink**) 06-02-10, 07:47

Quote:

Originally Posted by mac4rfree

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

mac4rfree · #14 (**permalink**) 06-02-10, 08:01

actually let me tell you what i did not understand..

If the data is

Quote:

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..

DB2Plus · #15 (**permalink**) 06-02-10, 08:13

Quote:

Originally Posted by mac4rfree

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

Internet Services

Web Development

Digital Marketing

Consumer Tech