# Select all records in which the sum of values are equal to a specific value

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
``````
• 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.) 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. Commented Dec 23, 2022 at 18:50

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

• 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. 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.) 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.) Commented Dec 23, 2022 at 21:37