counting comma separated values mysql-postgre

Question

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

emotions
sad, happy
happy, angry, boring
boring
sad, happy, boring, laugh

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

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

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

How does that data look like? Please share the table definition, sample input data, the expected output, and your attempts to resolve the problem — Nico Haase, Jul 17 '22 at 11:08
i am new here at stackoverflow and dont know how to create sample table. the data sample field called emotions, and each row values is comma separated like I wrote above. and the serve dat classifying the split of comma separated and count each values @NicoHaase — lonerhelios, Jul 17 '22 at 11:11
That sample table should simply contain the `CREATE TABLE` statement from your database — Nico Haase, Jul 17 '22 at 11:59
a) can you fix your data structure to not contain multiple values in one field? this query will not be the last one where you will get troubles with this data (and will be slow) b) do you have a list of possible values that can occur (preferably in another table, alternatively hard-codable)? — Solarflare, Jul 17 '22 at 12:08

Adrian Klaver · Answer 1 · 2022-07-17T16:09:06.063

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.

score 0 · Answer 2 · answered Jul 17 '22 at 16:10

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.

score 0 · Answer 3 · 2022-07-22T10:08:41.020

I've used MySQL 8.0, the query has no string limits. (Thanks to Ahmed for the intuition on recursive clause)

WITH RECURSIVE cte AS (
    SELECT ( LENGTH(REGEXP_REPLACE(emotions, ' ?[A-z]+ ?', ''))+1) AS n, emotions AS subs
    FROM feedback
    UNION ALL
    SELECT n-1 AS n, ( SUBSTRING_INDEX(subs, ', ', n-1) ) AS subs
    FROM cte
    HAVING n>0
)
SELECT SUBSTRING_INDEX(subs, ', ', -1) AS emotions, COUNT(subs) AS cnt
FROM cte
GROUP BY emotions

counting comma separated values mysql-postgre

3 Answers3