CODEWITHSUNDEEP
sqlite
Falling Into Infinity
Falling Into Infinity

Reputation: 737

sqllite select rows until a total amount is met in a column

I've seen the similar problem with mysql, but I barely could find any solution for the problem with sqllite.

My sample table,

-----------------------------
ID   | Product Name   | Price
-----------------------------
1            A           2
2            B           2
3            C           1
4            D           3
5            E           2

Here I need to get the rows until the total for the price column is equal or smaller than 5 in ascending order.

Upvotes: 2

Views: 1089

Answers (2)

Edper
Edper

Reputation: 9322

You could do a Running total using the Product ID and ORDER BY Product ID like the one below:

SELECT p1.ID, p1.ProductName, p1.Price,
(SELECT SUM(p2.Price) FROM Products p2 WHERE p1.ID >= p2.ID  ORDER BY p2.ID ) as RunningTotal
FROM Products p1
WHERE RunningTotal <= 5
ORDER BY p1.ID

See Fiddle Demo

Or using the Price and ORDER BY Price like one below:

SELECT p1.ID, p1.ProductName, p1.Price, 
(SELECT SUM(p2.Price) FROM Products p2 WHERE p1.Price >=  p2.Price ORDER BY Price ) 
as RunningTotal
FROM Products p1
WHERE RunningTotal <= 5
ORDER BY p1.Price;

See 2nd Fiddle Demo

Upvotes: 7

Anurag
Anurag

Reputation: 141879

It's probably best to do it in code as SQLite does not support an easy way to do cumulative sums as far as I know. You can create an index on the Price column.

Then running a query like

SELECT * FROM <table> ORDER BY Price

Note that this will not eagerly fetch all rows from the database, but just provide you with the cursor. Keep fetching the next row from the cursor until you reach the desired sum.

Upvotes: 0

Related Questions