CODEWITHSUNDEEP
mysqlgroup-by
Alexandr Kapshuk
Alexandr Kapshuk

Reputation: 1471

MySQL: group rows by sum of a binary variable

I have a table that I want to group by a binary variable group_generator that defines end of the group: if it equals 1, then the group contains all previous rows with group_generator = 0

Example:

Numbers        group_generator    
10             0    
20             0    
30             1    
40             0    
50             1 
60             1

I need Numbers grouped in three groups:

(10, 20, 30); 
(40, 50); 
(60)

I tried creating a new column with sum of group_generator for all rows with index less than current, like this:

Numbers        group_generator    group
10             0                  0
20             0                  0
30             1                  0
40             0                  1
50             1                  1
60             1                  2

and group by the last column, but that's complicated without temporary tables.

Is there an easy way do this in MySQL?

Upvotes: 0

Views: 685

Answers (1)

Nick
Nick

Reputation: 147206

Once you have your new column, this query will give you the desired result:

SELECT GROUP_CONCAT(Numbers) FROM table GROUP BY `group`

Output:

group_concat(numbers)
10,20,30
40,50
60

So the whole query could be:

SELECT GROUP_CONCAT(Numbers),
(SELECT IFNULL(SUM(group_generator), 0) FROM table1 t2 WHERE t2.id < table1.id) AS `group`
FROM table1
GROUP BY `group`

Output

group_concat(Numbers)   group
10,20,30                0
40,50                   1
60                      2

You could also produce this output with a stored procedure:

DELIMITER //
drop PROCEDURE if EXISTS groupit
//
create procedure groupit()
begin
   declare num int;
   declare numgroup varchar(1024) default '';
   declare gnum int default 0;
   declare pid int default 1;
   declare gg int;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET gg = -1;

   repeat
     select group_generator, Numbers  into gg, num from table2 where id=pid;
     if (gg >= 0) then
         set numgroup = concat(numgroup, if(numgroup='', '', ','), num);
         if (gg = 1) then
             select numgroup, gnum;
             set numgroup = '';
             set gnum = gnum + 1;
         end if;
     end if;
     set pid=pid+1;
   until gg = -1
   end repeat;
end
//
delimiter ;

Upvotes: 1

Related Questions