How to split, count and insert between two tables?

Question

I have a table like this:

GROUP             COUNT
tag1, tag2        23
tag1, tag2, tag3  12
tag2, tag3        10

I want to create a table like this:

TAG               COUNT
tag1              35
tag2              45
tag3              22

Basically, I want to split the string in the first table, then count the occurences and then insert into the new table. I am finding it increasingly difficult to split a string in MySQL. I don't mind taking the result to my PHP and working with it there, but the table is 32000 row big and for some reason, my PHP process enters an indefinite SLEEP mode.

So, any pointers on how I can achieve this using purely SQL commands will be much appreciated.

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-05-07T14:40:48

You can do this in SQL, but it requires a few tricks. You can extract the nth elements of a string in a list by doing:

select reverse(substring_index(reverse(substring_index(list, ', ', n)), ',', 1))

The innermost substring_index() retrieves everything up to the nth item. Then reverse the string and get the first item. Finally, reverse it again to undo the other reverse.

The second trick is to do a cross join to bring in a list of numbers. Your lists have at most 3 elements, so you need at most 3 in the list. The sample query does this by using union all to bring numbers together; you might have a numbers table of some sort.

The final step is to aggregate the data and sum things up:

select tag, SUM(count)
from (select reverse(substring_index(reverse(substring_index(group, ', ', n.n)), ',', 1)) as tag, count
      from t cross join
           (select 1 as n union all select 2 union all select 3
           ) n
      where n.n <= 1+(length(GROUP) - length(replace(group, ',', '')))
     ) t
group by tag

I did not backquote everything in the query. In general, it is bad practice to columns something like count or group that are reserved words in SQL.

How to split, count and insert between two tables?

1 Answers