How to categorize rows by SUM of a column in mysql?
One option might be using user-variables:

SET @folder = 1;
SET @items = 0;
SELECT id, num_items, 
  (SELECT IF(((@items:=@items+num_items)&gt;100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items&gt;100,@items:=0,@items) as checkItems
FROM foo;


Here's an sql fiddle of it, though your example id=4 does not match. In the first block, it's 44, in the expected output block it is 41, so the folders are off.


 Adding a caveat here. According to the documentation on user variables:


  As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.


Just keep that in mind.

Edit by RolandoMySQLDBA (2012-12-17 14:30 EDT)

So as to be sure of what the user variables look like, perhaps have each of the user variables printed along the way : ( See Example )

SET @folder = 1;
SET @items = 0;
SELECT id, num_items, 
  @folder as CurrentFolder_Before,
  @items as CurrentItems_Before,
  (SELECT IF(((@items:=@items+num_items)&gt;100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items&gt;100,@items:=0,@items) as checkItems,
  @folder as CurrentFolder_After,
  @items as CurrentItems_After
FROM foo;


DTest gets a +1 for his application of user variables.