Solved

progressive sum in db2 sql

Posted on 2011-09-30
21
1,031 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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