Stop SQL Select After Sum Reached

user_17312223 0
Viewed 116

My database is Db2 for IBM i.

I have read-only access, so my query must use only basic SQL select commands.

==============================================================

Goal:

I want to select every record in the table until the sum of the amount column exceeds the predetermined limit.

Example:

I want to match every item down the table until the sum of matched values in the "price" column >= $9.00.

enter image description here

The desired result:

enter image description here

Is this possible?

db2-400 ibm-midrange sum sql
1 Answers

You may use sum analytic function to calculate running total of price and then filter by its value:

with a as (
  select
    t.*,
    sum(price) over(order by salesid asc) as price_rsum
  from t
)
select *
from a
where price_rsum <= 9

SALESID | PRICE | PRICE_RSUM
------: | ----: | ---------:
   1001 |     5 |          5
   1002 |     3 |          8
   1003 |     1 |          9

db<>fiddle here

user_27787100
Related