mysql counting comma separated values at field

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

An identical question has been asked in the last 60 mins - why not just wait for that question to be answered? — NickW, Jul 17 '22 at 12:01

score 0 · Answer 1 · answered Jul 17 '22 at 12:16

You will want to have a table called 'Emotions' and each emotion has it's own primary key, and any other information that should be related to each emotion in columns, so that you can join them when necessary. Its terrible when you combine data that you want to give, retrieve in the same column when using SQL. Each column, row, and field between rows and columns should have one element. I recommend never to use a list with values using commas in one cell.

score 0 · Answer 2 · answered Jul 17 '22 at 12:40

here is what you need

SELECT  NAME AS emotions,COUNT(NAME) FROM (SELECT
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(emotions.emotions, ',', numbers.n), ',', -1)) name
FROM
  (SELECT 1 n UNION ALL SELECT 2
   UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN emotions
  ON CHAR_LENGTH(emotions.emotions)
     -CHAR_LENGTH(REPLACE(emotions.emotions, ',', ''))>=numbers.n-1
ORDER BY
  ID, n) emotions
  GROUP BY name

mysql counting comma separated values at field

2 Answers2