CODEWITHSUNDEEP
mysqlsql
Hardik Jain
Hardik Jain

Reputation: 13

Fetch records with sum less then or equal to values in dataset

This question is an extension of the question here Query to get all records until the sum of column less than or equal a value

For the table: items

slno item price
1 item1 1000
2 item2 2000
3 item3 3000
4 item4 4000
5 item5 5000
6 item6 6000

table: values

id value
1 3500
2 6000
3 10000
4 21000

Get all the records in items having sum(price) equal to or less then each number in table values

such that the result will be

id item
1 item1
1 item2
2 item1
2 item2
2 item3
3 item1
3 item2
3 item3
3 item4
4 item1
4 item2
4 item3
4 item4
4 item5
4 item6 
SELECT slno, item, price
  FROM
(
  SELECT slno, item, price, 
  (
    SELECT SUM(price)
      FROM table1
     WHERE slno <= t.slno
  ) total
    FROM table1 t
) q
 WHERE total <= 10000
 ORDER BY slno

above solution is for a single value of 10000 this solution was given by https://stackoverflow.com/users/1920232/peterm

how to get records for each value in table: values

Upvotes: 0

Views: 170

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a cumulative sum and then join:

select v.*, i.*
from (select i.*, sum(price) over (order by slno) as running_price
      from items i
     ) i join
     values v
     on i.running_price <= v.value
order by v.id, i.slno;

Upvotes: 1

Related Questions