Get the SUM from table

General PHP
1

Hello all,

I don’t know how to put the problem’s title, so just forget about the thread’s title. Now let’s say I have this table, let’s say its name is “tbl_cost”…

==========

id|name|cost

==========

1 |a | 2

2 |b | 3

3 |c | 5

4 |d | 3

5 |e | 1

6 |f | 3

I want to show list from tbl_cost that its cost’s sum is less or equal 10,

then I will got

1 | a |2

2 | b |3

3 | c |5

because 2+3+5=10

Currently, I solve this problem by looping the query till found the sum




$i = 0

Looping 

        SELECT sum(cost) FROM tbl_cost WHERE id <=$i

        $i++;

until found the sum
2

Do you want to find the first possible sum <= 10 (like in your example) or do you want any possible combination that would result in sum <= 10?

For example if you start at id 2:

2 |b | 3

3 |c | 5

(4 |d | 3) is not used cause 3+5+3 would be 11

3+5=8

3

Only the first possible, no need of combination…

4

If you know the minimal cost “min_cost”, then you’ll need to retrieve “10/min_cost” first rows from the table ordered by “cost” and do a simple loop in your program (but without querying database 100 times).

Also you solution is incorrect. Assume we have the following table:

(1, a, 12)

(2, b, 10)

(3, c, 4) and so on.

"SELECT sum(cost) FROM tbl_cost WHERE id <=$i" will never find a solution since 12+any cost > 10.

5

well because I want it to sum from the first record (no combination), null is an expected result for this case…

Btw, I don’t quite get your point there…