CODEWITHSUNDEEP
sqldatabasepostgresql
streetCoder3127917
streetCoder3127917

Reputation: 75

SQL query to search first rows until sum = value and skip big value that can exceed the value

I have a table

id | amount
---+--------
 1 |  500
 2 |  300
 3 |  750
 4 |  200
 5 |  500

I want to select rows ascending until the sum is 1000 or until all rows are searched (and skip a big value (750) that can exceed 1000).

How can I do query to return some rows like below?

Thanks for help

id | amount
---+--------
 1 |  500
 2 |  300
 4 |  200

Upvotes: 1

Views: 774

Answers (3)

GMB
GMB

Reputation: 222682

I think that you need a common table expression for this.

The idea is to do a cumulative sum that skips the rows that would cause the sum to go above 1000 (aliased sm in the CTE), and to flag the records to skip (aliased keep in the CTE). Then the outer query just filters on the flag.

with recursive cte as (
    select 
        id, 
        amount, 
        case when amount > 1000 then 0 else amount end sm,
        case when amount > 1000 then 0 else 1 end keep
    from mytable 
    where id = 1
    union all
    select 
        t.id, 
        t.amount, 
        case when c.sm + t.amount > 1000 then c.sm else c.sm + t.amount end,
        case when c.sm + t.amount > 1000 then 0 else 1 end
    from cte c
    inner join mytable t on t.id = c.id + 1
)
select id, amount from cte where keep = 1 order by id

Demo on DB Fiddle:

id | amount
-: | -----:
 1 |    500
 2 |    300
 4 |    200

Upvotes: 1

Frederic
Frederic

Reputation: 1028

you should get the expected result using a recursively common table expression..

doing something like this..

with RECURSIVE yourtableOrdered as (select row_number() over (order by id) row_num, id, val from (values (1, 500), (2, 300), (3, 750), (4, 200), (5, 500)) V (id, val)),
lineSum as (
    select row_num, id, val, 
    case when val <= 1000 then val else 0 end totalSum, 
    case when val <= 1000 then true else false end InResult
    from yourtableOrdered
    where row_num = 1
    union all
    select y.row_num, y.id, y.val, 
    case when previousLine.totalSum + y.val <= 1000 then previousLine.totalSum + y.val else previousLine.totalSum end totalSum, 
    case when previousLine.totalSum + y.val <= 1000 then true else false end InResult
    from yourtableOrdered y
    inner join lineSum previousLine
    on y.row_num = previousLine.row_num + 1
),
yourExpectedResult as (
    select * from lineSum where InResult = true
)
select * from yourExpectedResult

see a working sample in

http://sqlfiddle.com/#!17/2cbcf/1/0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Use a cumulative sum:

select t.*
from (select t.*,
             sum(amount) over (order by id) as running_amount
      from t
     ) t
where running_amount - amount < 1000;

Upvotes: 0

Related Questions