Solved

progressive sum in db2 sql

Posted on 2011-09-30
21
1,038 Views
Last Modified: 2012-05-12
Hi,
in DB2 (V5r4m0),
I have a table with a date and a number,
I would like to make a query which orders this table by date and add a field
which calculate the progressive sum, does it exist a function which makes this?
Table:
Jan/1 - 10
Jan/2 - 11
Jan/3 - 10
Feb/1 - 1
Mar/3 -5
Desired query Result:
Jan/1 - 10 - 10
Jan/2 - 11 - 21
Jan/3 - 10 - 31
Feb/1 - 1 - 32
Mar/3 - 5 - 37
0
Comment
Question by:bobdylan75
  • 8
  • 7
  • 2
  • +4
21 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36890305
I believe DB2 supports the ANSI Windowing functions with ROWS|RANGE framing. Therefore, you can give this a try.

SUM(YourValueColumn)
   OVER
   (
   ORDER BY YourDateColumn
   ROWS UNBOUNDED PRECEDING
   )

OR

SUM(YourValueColumn)
   OVER
   (
   ORDER BY YourDateColumn
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   )

Reference: OLAP functions
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023461.htm

Hope that helps!
0
 

Author Comment

by:bobdylan75
ID: 36890492
could you write the exact code?
Starting from this sql:
select fDate, fQty, ***** progressive sum of fQty ***** from LittleWarehouse

thanks,
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36890499
select fDate, fQty
     , sum(fQty) over(order by fDate rows unbounded preceding) fQty_RT
from LittleWarehouse
;

Open in new window

0
 

Author Comment

by:bobdylan75
ID: 36890511
very strange error,
it gives me: invalid token (,
, (comma) from into expected....

(after Over doesn't expect "("
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36890871

Hi Bob,


Let's let one of our AS/400 experts chime in.  I don't know when DB2 on the AS/400 added the OLAP functions.


Kent
0
 

Author Comment

by:bobdylan75
ID: 36891006
ok,
thanks ;)
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36891193
you can always achieve this using a join, for example

select t1.date_col, sum(t2.val_col)
from your_table t1
  join your_table t2
    on t1.date_col <= t2.date_col
order by t1.date_col
0
 

Author Comment

by:bobdylan75
ID: 36891217
I don't understand what kind of data are there in t2 or in t1?
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 200 total points
ID: 36891583
t1 is the base table
t2 is a table that contains for each record in t1, all the records up to that date
i had a mistake in the sql, try this instead


select t1.date_col, sum(t2.val_col)
from your_table t1
  join your_table t2
    on t1.date_col >= t2.date_col
order by t1.date_col
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36891593
Nice point, Kent. I thought the version cited was equivalent to UDB 9, but that was going off the a bad assumption that one it is all the features and two that version did indeed have full support for windowing. Yes, a join or sub query is possible. Since you have to use your table LittleWarehouse more than once in the query, you have to alias them so SQL will know which you are referring to when you call for column fQty for example.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36891626
I think you want GROUP BY fDate, fQty using the JOIN approach also.
0
 
LVL 18

Accepted Solution

by:
daveslash earned 300 total points
ID: 36892352

Momi and mwvisa1 nailed it, and it works beautifully on AS/400 (a.k.a. System i or iSeries)

select *
  from deleteme

ADATE              AVALUE
2011-02-01              1
2011-02-02              2
2011-02-03              3
2011-02-04              4
2011-02-05              5

select t1.aDate,           
       sum(t2.aValue)
  from deleteme t1   
  join deleteme t2   
    on t1.aDate >= t2.aDate
 group by t1.aDate

ADATE                 SUM
2011-02-01              1
2011-02-02              3
2011-02-03              6
2011-02-04             10
2011-02-05             15

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
ID: 36893691
No real help from me. I'm only noting that V5R4 was initial OLAP support in the standard DB2 on AS/400s. (It was available earlier, but for extra cost.) Detailed syntax isn't quite the same inside of OVER(), and I don't have a working example for this case. Nor am I clear on exactly what the statement should be in V5R4 -- I personally only have V5R3 available for my experimentation.

Tom
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 36893896
This ins't really an answer to the question, but a note about documentation.

I noticed a documentation link above in mwvisa's post to the "DB2 UDB LUW OLAP Function Guide".  The
"LUW" stands for Linux, Unix, and Windows, not the AS/400 (iSeries, i5, IBM i).  Unfortunately, AS/400 DB2 differs significantly on some points from LUW and DB2 for z, so readers will save a lot of frustration if they use the appropriate documentation for the DB2 version they are using.

DB2 for AS/400 / iSeries / IBM i information is found in the IBM (AS/400, iSeries, i) Information Center for your release, under the Database topic.  This is the link for the V5R4 Information Center:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp

The "What's New" topic in the Database section has a great introduction to the OLAP features added in V5R4, with examples.

If you look at the list of Database - Printable PDF's, you'll find the SQL Programmer's Guide, and the SQL Reference, which are the two "main" SQL references for the system:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafykickoff.htm

The process for other OS versions is similar: Go to the Information Center for your OS Version (google "IBM Information Center V6R1", for example), expand the Database topic, and look for the SQL Programming and SQL Reference manuals.

- Gary Patterson


0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36894241
Very nice, Gary! It appears from the documentation, OLAP support was added for the basic windowing functions but NOT with the framing which makes the running totals work as I showed; therefore, the JOIN approach will have to be used. If you have a large row set, it will be a little inefficient; however, I am sure the AS/400 has plenty of processing power to handle that ... :)
0
 

Author Comment

by:bobdylan75
ID: 36915314
@momi_sabag and daveslash
Sorry, but your code, which is so easy,
doesn't work in my v5r4m0 os400 machine.
0
 

Assisted Solution

by:bobdylan75
bobdylan75 earned 0 total points
ID: 36915360
@momi_sabag and daveslash.
with my modifications, your solution works properly!
thanks!

select t1.fdate, t1.fvalue, sum(t2.fvalue)
from deleteme t1                  
  join deleteme t2                
    on t1.fdate >= t2.fdate                
group by t1.fdate,t1.fvalue              
order by t1.fdate                        
0
 

Author Closing Comment

by:bobdylan75
ID: 36938239
thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36917070
Glad you figured that out. I guess on 9/30, I should have been more clear that what I was saying is the join approach would not work without the group by. Anyway, happy it is all working for you now.

--Kevin
0
 

Author Comment

by:bobdylan75
ID: 36917140
Sorry mwvisa1.
I have tried to change points attribution but it's difficult.
I ask to the moderator to do so for me.
thank you everybody.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36917280
Not a problem. I posted that more as an apology for you as it could have saved you six days. I meant what I said, though, the important part is you got a working solution.

Best regards and happy coding,

Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now