CODEWITHSUNDEEP
phpmysqlsqlsum
Crookie Susoku
Crookie Susoku

Reputation: 53

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.

Upvotes: 4

Views: 4818

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Edit: When there are duplicate values in the storage column, it has to be accounted for in the running sum by including a condition for the id column. (in this case < condition has been used, so the smallest id for a duplicate storage value gets picked up)

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

Upvotes: 2

Victorqedu
Victorqedu

Reputation: 494

This is what you need:

SET @suma = 0;
SELECT @suma:=@suma+`storage`, id, storage FROM table 
WHERE @suma<=410000 
ORDER BY storage ASC;

I added "ORDER BY storage ASC" to skip rows that have to much storage.

Upvotes: 1

Related Questions