Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# progressive sum in db2 sql

Posted on 2011-09-30
Medium Priority
1,099 Views
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
Question by:bobdylan75
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 7
• 2
• +4

LVL 60

Expert Comment

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

Hope that helps!
0

Author Comment

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

thanks,
0

LVL 60

Expert Comment

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

Author Comment

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

(after Over doesn't expect "("
0

LVL 46

Expert Comment

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

ID: 36891006
ok,
thanks ;)
0

LVL 37

Expert Comment

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

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

LVL 37

Assisted Solution

momi_sabag earned 800 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

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 60

Expert Comment

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

LVL 60

Expert Comment

ID: 36891626
I think you want GROUP BY fDate, fQty using the JOIN approach also.
0

LVL 18

Accepted Solution

Dave Ford earned 1200 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

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

sum(t2.aValue)
from deleteme t1
join deleteme t2

2011-02-01              1
2011-02-02              3
2011-02-03              6
2011-02-04             10
2011-02-05             15
``````
0

LVL 27

Expert Comment

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 35

Expert Comment

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 60

Expert Comment

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

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

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

ID: 36938239
thanks
0

LVL 60

Expert Comment

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

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 60

Expert Comment

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

Question has a verified solution.

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

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.â€¦
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signalâ€¦
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll