Question 89451 in PostgreSQL Question posted in
The official documentation can be found here.

Find all rows in PSQL query that adds upto next possible sum above target – Postgresql

Like in below if my target_sum is

  • 5 it should return id 1
  • 8 then it should return id 1 and 2
  • 13 then it should return id 1, 2 and 3

Basically my query should return the next possible sum above my target_sum how to modify below query

id marks
1 5
2 7
3 6
4 9 
SELECT *
    FROM (
        SELECT *,
               SUM(marks) OVER (ORDER BY id) AS cum_sum
        FROM students
    ) t
    WHERE cum_sum <= target_sum
    ORDER BY id
Tags:

2

Answers


  1. 0

    Add one to the cum_sum and use LAGto pass it in the next row

    Data

    id|marks|cum_sum|cum_sum_lag|
--+-----+-------+-----------+
 1|    5|      5|          0|
 2|    7|     12|          6|
 3|    6|     18|         13|
 4|    9|     27|         19|

    Query

    SELECT * FROM (
    SELECT id, marks, cum_sum,
       lag(cum_sum+1,1,0) OVER (ORDER BY id) AS cum_sum_lag
    FROM (
        SELECT *,
               SUM(marks) OVER (ORDER BY id) AS cum_sum
        FROM students
    )  t1
) t2
WHERE cum_sum_lag <= :target_sum
ORDER BY id
    Login or Signup to reply.
  2. 0

    This statement does the trick.

    select id, marks
from (
  select *,
    lag(sum,1,0) over (order by id) lag_sum
  from (
    select 
      *,
      sum(marks) over (order by id) sum
    from demo
  ) x 
) y
where lag_sum < 8 -- <-- target sum goes here
order by id;

    It uses the lag function to access the sum of the previous row.

    DB fiddle to play with the query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search