mysql sum()

until amount reaches desired value

[mcibor]

I have a table with let's say weight and amount:

id, amount, weight
1, 10, 100;
2, 5, 78;
3, 10, 105;

I would like to create a new record summarizing the above table, but amount must not exceed 20.

For now I'm doing this with php:

//$ask = querry...
while(1)
{
$row = mysql_fetch_array($ask);
$sum_amount += $row["amount"];
if($sum_amount > 20) break;

$sum_weight += $row["weight"];
$id_taken += $row["id"].", ";
}

//INSERT INTO table(sum_amount, sum_weight) VALUES('$sum_amount', '$sum_weight');
//DELETE FROM table WHERE id IN ($id_taken 0) //0 is because last char is a comma ', '

Is there a way to squeeze it somehow?

Michal Cibor

[ergophobe]

So you want
- all combos less than 20
- just the first n records that yield 20
- a series where records 1-n add up to 20, then n+1 - m add up to 20

You can't use SUM() or the result (i.e. SUM(col) as sum_col) in a where, so I'm not sure how you could get the collection of sums adding up to 20.

One little thing in the php BTW, don't you want to fail *before* you add the row amount, so that the sum_amount and the sum_weight are for the same number of rows?

while($row = mysql_fetch_array($result)) 
{ 
 if( ($sum_amount + $row['amount']) < 20) 
 { 
  $sum_amount += $row["amount"]; 
  $sum_weight += $row["weight"]; 
  $id_taken += $row["id"].", "; 
 } 
}  
$id_taken = substr($id_taken, 0, -1); //gets rid of the comma once and for all. 

[mcibor]

Thanks ergophobe!

I totally forgot that I was suming that up before breaking. And this way is even better than breaking in the mid.

That was helpful

PS I didn't think it would be possible to do all in mysql.

Best regards!
Michal Cibor