Mysql – How to categorize rows by SUM of a column in thesql

MySQL

I have a table as

id  num_items  folder
1   4
2   33
3   74
4   41
5   24
6   34
7   46
8   55
9   11

I want to assign folder number to rows but limiting the number of items in each folder (e.g. 100). Thus, increasing the folder number (next folder) if SUM(num_items) > 100.

The UPDATEd folder should look like

id  num_items  folder
1   4          1
2   33         1
3   74         2
4   41         3
5   24         3
6   34         3
7   46         4
8   55         4
9   11         5

Since, SUM make the calculation for the entire column, apparently, I need to reset the value/process of SUM after reaching the maximum item in folder.

Best Answer

One option might be using user-variables:

SET @folder = 1;
SET @items = 0;
SELECT id, num_items, 
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items>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)>100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items>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.

Related Question