How to concatenate a column after x rows?

Question

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 > 20) { // 20 is a given value
    $mysqli->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,....

Could you give an example of what this looks like after you do this operation?
@FrustratedWithFormsDesigner I added a simple example to clarify the process
Ok that's good. What's the query that drives your loop? Is there any ordering on it or could these records appear in any order?
OK now I am confused. Whenever, there is a break in the sum, you want it grouped again. Is that correct?
@RolandoMySQLDBA yes, exactly! I want to reset the sum, and calculate the sum to reach the next 20. In fact, this is a loop of INSERTs for groups reaching the sum of frequency of 20 after the last INSERT.

RolandoMySQLDBA · Accepted Answer · 2012-12-04 22:34:30Z

Basically, you want to result the running total and group it

This requires iterative variable manipulation.

First, your sample data quadrupled:

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

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

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

mysql> insert into table1 values
    -> ('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> insert into table1 select * from table1;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

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

mysql> 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>

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 >= @freq_limit,1,0)           bb,
        @freq_count    := IF(@freq_count >= @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> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) 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>

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> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @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;
+------+---------------------------------+
| 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>

Just set @freq_limit to whatever value you need.

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

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

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

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

mysql> insert into table1 values
    -> ('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> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @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;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object5                         |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!

a1ex07 · Answer 2 · 2012-12-04 21:51:24Z

up vote 1 down vote

Unless I misunderstood you, it should work :

INSERT INTO table2(concatenated_column) 
SELECT GROUP_CONCAT(object) FROM table1 
HAVING SUM(frequency)>20;

answered Dec 4 '12 at 21:51

a1ex07
2,50829

	This is way more direct. + 1 !!! – RolandoMySQLDBA Dec 4 '12 at 21:52
	This is brilliant, but this will work for one `INSERT` when the sum of frequency reaches the value. But I want to repeatedly `INSERT` and reset the `sum of frequency` – All Dec 4 '12 at 22:04

asked	5 months ago
viewed	155 times
active	5 months ago

How to concatenate a column after x rows?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged mysql sql innodb mysql-5.5 functions or ask your own question.

Community Bulletin

How to concatenate a column after x rows?

2 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql sql innodb mysql-5.5 functions or ask your own question.

Community Bulletin

Related