I have a column called "feedback", and have 1 field called "emotions". In those emotions field, we can see the random values and random length like

etc with different values and different length. so, the question is, what's query to serve the mysql or postgre data:

based on SQL: Count of items in comma-separated column in a table we could try using

    SELECT value as [Holiday], COUNT(*) AS [Count]
FROM OhLog
CROSS APPLY STRING_SPLIT([Holidays], ',')
GROUP BY value

but it wont help because that is for sql server, not mysql or postgre. or anyone have idea to translation those sqlserver query to mysql? thank you so much.. I really appreciate it

CodePudding user response：

Using Postgres:

create table emotions(id integer, emotions varchar);

insert into emotions values (1, 'sad, happy');
insert into emotions values (2, 'happy, angry, boring');
insert into emotions values (3, 'boring');
insert into emotions values (4, 'sad, happy, boring, laugh');

select 
  emotion, count(*) 
from 
  (select 
     trim(regexp_split_to_table(emotions, ',')) as emotion 
  from emotions) as t 
group by 
   emotion;

 emotion | count 
--------- -------
 happy   |     3
 sad     |     2
 boring  |     3
 laugh   |     1
 angry   |     1

From String functions regexp_split_to_table will split the string on ',' and return the individual elements as rows. Since there are spaces between the ',' and the word use trim to get rid of the spaces. This then generates a 'table' that is used as a sub-query. In the outer query group by the emotion field and count them.

CodePudding user response：

Try the following using MySQL 8.0:

  WITH recursive numbers AS 
  (
     select 1 as n
     union all
     select n   1 from numbers where n < 100
  )
   ,
  Counts as (  
  select trim(substring_index(substring_index(emotions, ',', n),',',-1)) as emotions
  from Emotions
  join numbers
  on char_length(emotions) - char_length(replace(emotions, ',', '')) >= n - 1
)
select emotions,count(emotions) as counts from Counts
group by emotions
order by emotions

See a demo from db-fiddle.

The recursive query is to generate numbers from 1 to 100, supposing that the maximum number of sub-strings is 100, you may change this number accordingly.