CODEWITHSUNDEEP
sqlpostgresqlsumlimit
mentatkgs
mentatkgs

Reputation: 1601

How do I limit a select by a sum?

I want to select all the cheapest toys of my stock, amounting a total of 10.0 USD:

That is, I want to do something that looks like this:

select * from toy where sum(price) < 10.0 order by price;

What would be the correct SQL?

To make it clearer, I'll add an example. Suppose I have these items in my table:

       name       | price
------------------+-------
car               |     1
boat              |     2
telephone         |     8
gold bar          |    50

The result I would be: 1 car and 1 boat.

Totaling the price of 3 USD. I cannot select the telephone because it would amount 13 USD, and that is larger than 10.

Any Ideas?

Upvotes: 6

Views: 1345

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

SQL Fiddle

select name, price, total
from (
    select
        name, price,
        sum(price) over(
            order by price
            rows between unbounded preceding and current row
        ) total
    from toy
) s
where total <= 10
order by price

Notice that while between unbounded preceding and current row is the default frame, the default mode is range. So it is necessary to have at least rows unbounded preceding declared as the current row is the frame end default.

Upvotes: 4

user359040
user359040

Reputation:

Try:

SELECT a.name, max(a.price) price 
FROM Toy a
JOIN Toy b
  on a.price > b.price or (a.price=b.price and a.name>=b.name)
GROUP BY a.name
HAVING SUM(b.price) <= 10.0
order by 2

SQLFiddle here.

Upvotes: 4

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Here is implementation using recursive CTE. There are other solutions for this, you can google for "running totals".

WITH RECURSIVE CTE_RN  AS 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY Price) RN FROM Toys
)
, CTE_Rec AS
(
    SELECT name, price, rn FROM CTE_RN WHERE RN = 1
    UNION ALL
    SELECt r.name, a.price + r.price as price, r.rn FROM CTE_RN r
    INNER JOIN CTE_Rec a on a.RN + 1 = r.RN
    where a.price+r.price <= 10
 )
SELECT name, price as total_price FROM CTE_Rec

SQLFiddle Demo

PS: It greatly depends on RDBMS, so that's why it was important to include that info at the start.

Upvotes: 2

Related Questions