Crookie Susoku Crookie Susoku - 1 year ago 65
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 Source

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<,0) < 410000
order by storage

SQL Fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download