Crookie Susoku Crookie Susoku - 4 months ago 8
MySQL Question

SQL: How to select rows that sum up to certain value

I want to select rows that sum up to a certain value.

My SQL (SQL Fiddle):

id user_id storage
1 1 1983349
2 1 42552
3 1 367225
4 1 1357899
37 1 9314493


It should calculate the sum up to 410000 and get the rows. Meanwhile it should get something like this:

id user_id storage
2 1 42552
3 1 367225


As you can see, 42552 + 367225 = 409777. It selected two rows that are nearly 410000.

I have tried everything but it didn't work :(

Sorry for my language, I am German.

vkp vkp
Answer

You can use a correlated subquery to get the running total and retrieve those rows whose running total is < a specified number. (note that i changed the storage column to int. if it is a varchar the comparison would return the wrong result)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage),0) < 410000
order by storage

SQL Fiddle