Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,....
share|improve this question
    
Could you give an example of what this looks like after you do this operation? –  FrustratedWithFormsDesigner Dec 4 '12 at 21:46
    
@FrustratedWithFormsDesigner I added a simple example to clarify the process –  All Dec 4 '12 at 21:50
    
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? –  FrustratedWithFormsDesigner Dec 4 '12 at 21:52
1  
OK now I am confused. Whenever, there is a break in the sum, you want it grouped again. Is that correct? –  RolandoMySQLDBA Dec 4 '12 at 21:56
1  
@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. –  All Dec 4 '12 at 22:06

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

Unless I misunderstood you, it should work :

INSERT INTO table2(concatenated_column) 
SELECT GROUP_CONCAT(object) FROM table1 
HAVING SUM(frequency)>20;
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.