3

I have a products table that looks like this (the other fields are hidden for simplicity)

id | price
----------
1  |  199
2  |  50
3  |  320
4  |  120

I'm trying to create a query that selects all products in which values are equal to or less than a specific value, for example:

A customer wants to spend $600 at max, the first 3 products of the example should be returned (199+50+320 = 569)

I tried with the query below but got no success

select * from products group by id having sum(price) <= 600
2
  • Caution: In an extreme case, one can devise a list of only 40 items where the resultset would have over a trillion combinations! (Simply have the max be greater than the sum of the individual prices. That leads to 2^40-1 result rows.)
    – Rick James
    Commented Dec 23, 2022 at 18:31
  • Instead of "... selects all products in which ..." do you mean "... selects every combination of products in which ..." or " "... selects some combination of products in which ..." or "... that comes closest to 600". Please tighten up the Question.
    – Rick James
    Commented Dec 23, 2022 at 18:50

1 Answer 1

5

I'm trying to create a query that selects all products in which values are equal to or less than a specific value

We need to create a cumulative sum for this case.

select id,
       price
from ( SELECT id,
              price,
              SUM(price) OVER(ORDER BY id asc ) AS cumulative_sum
       FROM products
     ) cum_sum
where cumulative_sum <=600;

https://dbfiddle.uk/8A46Gmcy

Note, above cumulative sum is ordered by the id which means it will test the first products.

If id 1 is equal to 601 query will return empty set.

You can change the order of the cumulative SUM based on your needs.

 SUM(price) OVER(ORDER BY price asc ) AS cumulative_sum  ---will start from the lowest price to max

Edit A more suitable answer/question would be finding every possible combination of the values which the sum is less than 600.

with recursive cte as ( 
    select id as max_id,
           price,
           cast(id as char(255))  as possible_combination 
           from products
  union all
    select p.id as max_id,
           c.price + p.price as price,
           cast(concat(c.possible_combination ,',' , p.id) as char(255))  as possible_combination
   from cte c
   inner join products p on p.id > c.max_id                       
)
select possible_combination,price
from cte
where price <= 600 ;

https://dbfiddle.uk/IaqXBqzi

7
  • If the goal is to find some combination of items that add up to under 600, this fails miserably. It only finds certain consecutive items.
    – Rick James
    Commented Dec 23, 2022 at 18:47
  • It seems to return the starting item of a list that ends with the last item. I see that as a useless goal, so downvoting. (If you sufficiently rewrite your Answer, I will remove my downvote.)
    – Rick James
    Commented Dec 23, 2022 at 18:52
  • @RickJames edited with a solution for every possible combination which I guess would be a proper answer Commented Dec 23, 2022 at 20:19
  • @RickJames what do you mean by one combination? It works fine here. Am I missing something? Commented Dec 23, 2022 at 21:32
  • Your recursive CTE seems correct. I was merely commenting that hardly any combinations are excluded as >600, namely 1,3,4 and 1,2,3,4. (I wonder what the OP can do with the results.)
    – Rick James
    Commented Dec 23, 2022 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.