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 as follows:

  TAG COUNT
 tag1 35
 tag2 45
 tag3 22

Basically, I want to split a row in the first table, then count the occurrences, and then insert into a new table. It's getting harder for me to break a string in MySQL. I don’t mind accepting the result in my PHP and working with it there, but the table of 32,000 rows is large, and for some reason my PHP process enters an undefined SLEEP mode.

That way, any pointers to how I can achieve this using exclusively SQL commands will be greatly appreciated.

Answer 1

You can do this in SQL, but it requires a few tricks. You can extract the nth elements of a row 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 element. Then change the line and get the first element. Finally, undo it again to undo another reverse.

The second trick is to do a cross join to enter a list of numbers. Your lists contain no more than 3 items, so you need no more than 3 in the list. The sample request does this using union all to combine numbers; you may have a table of numbers.

The last step is to collect the data and summarize them:

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

I did not answer all the questions in the request. In general, this is bad practice for columns like count or group , which are reserved words in SQL.