SQL query to get all records with sum less than 10000

Question

I have data in a table which looks like the below data set.

I want to get a group of those items whose price sum is less than 10000.

CREATE TABLE Table1
(slno int, item varchar(10), price int);

INSERT INTO Table1
(slno, item, price)
VALUES
(1, 'item1', 1000),
(2, 'item2', 2000),
(3, 'item3', 3000),
(4, 'item4', 4000),
(5, 'item5', 5000),
(6, 'item6', 6000),
(7, 'item7', 10000),
(8, 'item8', 2000),
(9, 'item9', 8000),
(10, 'item10', 2500),
(11, 'item11', 9000),
(12, 'item12', 1000),
(13, 'item13', 2500),
(14, 'item14', 2500),
(15, 'item15', 2500);

My sql query looks like this:

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 <= 1000
   ORDER BY item

It's not giving the expected result though, it's giving only one set of records:

 (1, 'item1', 1000),
 (2, 'item2', 2000),
 (3, 'item3', 3000),
 (4, 'item4', 4000)

whereas I need it to give me something like this:

 1ST SET

 (1, 'item1', 1000),
 (2, 'item2', 2000),
 (3, 'item3', 3000),
 (4, 'item4', 4000)

 2ND SET
 (7, 'item7', 10000),

@GordonLinoff

You need to name the `SUM(price)` column, and use that name in the condition. (Or switch to HAVING.) — jarlh, Oct 02 '20 at 10:55
https://stackoverflow.com/q/23166486/11683? https://stackoverflow.com/q/28202429/11683? — GSerg, Oct 02 '20 at 10:56
Queries return *one* result set, not multiples. You're asking how to generate combinations of records whose sum comes as close as possible to 10K. That's not trivial. This is called the [knapsack problem](https://en.wikipedia.org/wiki/Knapsack_problem) — Panagiotis Kanavos, Oct 02 '20 at 10:57

score 1 · Accepted Answer · answered Oct 02 '20 at 10:58

1

This type of operation requires a recursive CTE. In this case, you can assign a group to each row using such logic. The following assumes that slno has no gaps as in your example data:

with cte as (
      select slno, item, price, 1 as grp, price as running_price
      from table1
      where slno = 1
      union all
      select t1.slno, t1.item, t1.price,
             (case when t1.price + cte.running_price > 10000 then grp + 1 else grp end),
             (case when t1.price + cte.running_price > 10000 then t1.price else cte.running_price + t1.price end)
      from cte join
           table1 t1
           on t1.slno = cte.slno + 1
     )
select *
from cte
order by slno;

Here is a db<>fiddle.

answered Oct 02 '20 at 10:58

Gordon Linoff

1,242,037
58
646
786

@DineshTripathi that comment isn't readable. You should either update your question (with an [edit], or provide it in a consumable format; perhaps via a fiddle. – Thom A Oct 02 '20 at 11:05
@Gordon-Linoff the group 8 is having records which exceed total greater than 10000 . it should have been only less than or equal to 10000 – Dinesh Tripathi Oct 02 '20 at 12:06
@DineshTripathi . . . This divides the data into separate groups, each of which is either rows that sum to less than 10,000 or consists of one row (can't be smaller). That seems like a reasonable interpretation of your question. In the example, Group 8 has a total of 7,500 which is less than 10,000. – Gordon Linoff Oct 02 '20 at 12:14

SQL query to get all records with sum less than 10000

1 Answers1