How to concatenate a column after x rows? I have a table with columns

object varchar(255),
frequency tinyint(3) unsigned,


I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as

.. query loop {
$object .= $row['object'].",";
$frequency = $frequency+$row['frequency'];

if($frequency &gt; 20) { // 20 is a given value
    $mysqli-&gt;query("INSERT INTO table2 (concatenated_column) VALUES ('$object')");
    $frequency=0;
    $object='';
    }
}


Is it possible to do this with a native query of mysql instead of bringing the data into PHP and returning back to mysql?

Since the process is simple, I think mysql has enough functions to do so, but I do not know how to do so.

In fact, I want to group concatenate the entire column and split it when sum of frequency reaches the given value (i.e. 20). This needs a loop of INSERTs in which the sum of frequency reset to 0 upon reaching 20.

UPDATE: The process with do as:

table1:

object    frequency
object1   7
object2   5
object3   6
object4   8
object5   2


table2:

concatenated_column
object1,object2,object3,object4
object5,....
Basically, you want to result the running total and group it

This requires iterative variable manipulation.

First, your sample data quadrupled:

mysql&gt; drop database if exists all_db;
Query OK, 1 row affected (0.03 sec)

mysql&gt; create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use all_db
Database changed
mysql&gt; create table table1
    -&gt; (
    -&gt;     object varchar(255),
    -&gt;     frequency tinyint unsigned
    -&gt; );
Query OK, 0 rows affected (0.06 sec)

mysql&gt; insert into table1 values
    -&gt; ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql&gt; insert into table1 select * from table1;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql&gt; insert into table1 select * from table1;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql&gt; select * from table1;
+---------+-----------+
| object  | frequency |
+---------+-----------+
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
+---------+-----------+
20 rows in set (0.00 sec)

mysql&gt;


Let's make the query that generates group values:

set @freq_limit = 20;
set @freq_count = 0;
set @freq_group = 1;
set @freq_sum   = 0;
select cc,GROUP_CONCAT(object) objects from 
(
select * from 
(
    select *,
        @freq_groupinc := IF(@freq_count &gt;= @freq_limit,1,0)           bb,
        @freq_count    := IF(@freq_count &gt;= @freq_limit,0,@freq_count) aa,
        @freq_group    := @freq_group + @freq_groupinc                 cc,
        @freq_count    := @freq_count + frequency                      dd
    FROM table1
) AA) A GROUP BY cc;


Would you believe it works?

First I'll let you see the inner subquery's output

mysql&gt; select * from
    -&gt; (
    -&gt;     select *,
    -&gt;         @freq_groupinc := IF(@freq_count &gt;= @freq_limit,1,0)           bb,
    -&gt;         @freq_count    := IF(@freq_count &gt;= @freq_limit,0,@freq_count) aa,
    -&gt;         @freq_group    := @freq_group + @freq_groupinc                 cc,
    -&gt;         @freq_count    := @freq_count + frequency                      dd
    -&gt;     FROM table1
    -&gt; ) AA;
+---------+-----------+----+------+------+------+
| object  | frequency | bb | aa   | cc   | dd   |
+---------+-----------+----+------+------+------+
| object1 |         7 |  0 |    0 |    1 |    7 |
| object2 |         5 |  0 |    7 |    1 |   12 |
| object3 |         6 |  0 |   12 |    1 |   18 |
| object4 |         8 |  0 |   18 |    1 |   26 |
| object5 |         2 |  1 |    0 |    2 |    2 |
| object1 |         7 |  0 |    2 |    2 |    9 |
| object2 |         5 |  0 |    9 |    2 |   14 |
| object3 |         6 |  0 |   14 |    2 |   20 |
| object4 |         8 |  1 |    0 |    3 |    8 |
| object5 |         2 |  0 |    8 |    3 |   10 |
| object1 |         7 |  0 |   10 |    3 |   17 |
| object2 |         5 |  0 |   17 |    3 |   22 |
| object3 |         6 |  1 |    0 |    4 |    6 |
| object4 |         8 |  0 |    6 |    4 |   14 |
| object5 |         2 |  0 |   14 |    4 |   16 |
| object1 |         7 |  0 |   16 |    4 |   23 |
| object2 |         5 |  1 |    0 |    5 |    5 |
| object3 |         6 |  0 |    5 |    5 |   11 |
| object4 |         8 |  0 |   11 |    5 |   19 |
| object5 |         2 |  0 |   19 |    5 |   21 |
+---------+-----------+----+------+------+------+
20 rows in set (0.00 sec)

mysql&gt;


Look at the cc column. It increments every time the sum exceeds @freq_limit (20) and resets.

Now just run GROUP_CONCAT on object, grouping by cc

mysql&gt; set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select cc,GROUP_CONCAT(object) objects from
    -&gt; (
    -&gt; select * from
    -&gt; (
    -&gt;     select *,
    -&gt;         @freq_groupinc := IF(@freq_count &gt;= @freq_limit,1,0)           bb,
    -&gt;         @freq_count    := IF(@freq_count &gt;= @freq_limit,0,@freq_count) aa,
    -&gt;         @freq_group    := @freq_group + @freq_groupinc                 cc,
    -&gt;         @freq_count    := @freq_count + frequency                      dd
    -&gt;     FROM table1
    -&gt; ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object2,object3,object5,object1 |
|    3 | object4,object5,object1,object2 |
|    4 | object5,object1,object3,object4 |
|    5 | object2,object3,object4,object5 |
+------+---------------------------------+
5 rows in set (0.00 sec)

mysql&gt;


Just set @freq_limit to whatever value you need.

To show it works for just the 5 rows you supplied:

mysql&gt; drop database if exists all_db;
Query OK, 1 row affected (0.04 sec)

mysql&gt; create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use all_db
Database changed
mysql&gt; create table table1
    -&gt; (
    -&gt;     object varchar(255),
    -&gt;     frequency tinyint unsigned
    -&gt; );
Query OK, 0 rows affected (0.05 sec)

mysql&gt; insert into table1 values
    -&gt; ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql&gt; set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select cc,GROUP_CONCAT(object) objects from
    -&gt; (
    -&gt; select * from
    -&gt; (
    -&gt;     select *,
    -&gt;         @freq_groupinc := IF(@freq_count &gt;= @freq_limit,1,0)           bb,
    -&gt;         @freq_count    := IF(@freq_count &gt;= @freq_limit,0,@freq_count) aa,
    -&gt;         @freq_group    := @freq_group + @freq_groupinc                 cc,
    -&gt;         @freq_count    := @freq_count + frequency                      dd
    -&gt;     FROM table1
    -&gt; ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object5                         |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql&gt;


Give it a Try !!!