Select ROWS Until RUNNING TOTAL meet condition



IBM's flagship relational database management system

Select ROWS Until RUNNING TOTAL meet condition

Postby xboss » Fri Dec 27, 2013 11:49 am

One of the column in my table has Individual cost of an item.

Table: SAMPLE
NO. Item Cost ($)
1. A 30.00
2. B 50.00
3. C 40.00
4. D 25.00
5. E 15.00
6. F 35.00

Now I want to write a query to select rows from top
until I meet my budget of $100.00. In this case only
2 rows should be selected in my new table.

Any suggestion??

So far my query (not working offcourse)
SELECT ITEM, (SELECT SUM(COST) FROM SAMPLE S1 WHERE
   S1.NO = S2.NO AND S1.ITEM = S2.ITEM) AS RUNNINGTOTAL
             FROM SAMPLE S2;
xboss
 
Posts: 79
Joined: Mon Nov 29, 2010 10:55 am
Has thanked: 0 time
Been thanked: 0 time

Re: Select ROWS Until RUNNING TOTAL meet condition

Postby NicC » Fri Dec 27, 2013 2:59 pm

Use a cursor and loop through the returned rows until condition is met. By the way, why do you not expect item D in your result as it + A and B comes to $95.00?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Select ROWS Until RUNNING TOTAL meet condition

Postby xboss » Fri Dec 27, 2013 8:42 pm

I haven't used cursor yet. So, that will be new thing for me to explore (thanks for that) but I am merely trying to accomplish this using SQL (if possible). I am not expecting item D in my result. I am only expecting Item A and B because going further down would exceed by budget. Thanks for your reply.
xboss
 
Posts: 79
Joined: Mon Nov 29, 2010 10:55 am
Has thanked: 0 time
Been thanked: 0 time

Re: Select ROWS Until RUNNING TOTAL meet condition

Postby Terry Heinze » Sat Dec 28, 2013 3:06 am

I suspect NicC was assuming your logic would skip items that causes your accumulation to exceed the budget, and would keep looking for items that would NOT exceed it.
.... Terry
Terry Heinze
 
Posts: 239
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 12 times
Been thanked: 11 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post