How to split string token and group by count them in mysql?

Question

How to split the string and grouping them by splited token? I want to get that grouping splited token's each count.

I have a varchar column and it store a string which can split by ',' . below is the row data of the column. (column name is LogData)

[LogData]
1,2,3,4

1,3,1,9

2,1,3

6,2

And then i want to show(select) like below.

[token] [count]
  1    : 4
  2    : 3
  3    : 3
  4    : 1
  6    : 1
  9    : 1

If possible, then may i have a answer about this with some explanation? (I'm not skilled in db)

Hi, do you have to do this using `mysql`? Why don't you use `PHP` or something like that? — Hamza Abdaoui, Jan 22 '18 at 07:45
I'm using asp.net core and i must use grouping and order by (sorting by count). Uhm..actually I can do it in server side (c# asp.net core) but, if it's possible i want to use sql. Because this is a dash board and mysql's grouping and sort algorithm is much faster than mine. (maybe) — heapoverflow, Jan 22 '18 at 07:52
https://stackoverflow.com/questions/12872277/split-comma-separated-value-from-table-column-into-rows-using-mysql?rq=1 — undefined_variable, Jan 22 '18 at 07:52
Uhm.. thanks but it is a llitle bit different with my question.. @undefined_variable — heapoverflow, Jan 22 '18 at 07:58

score 1 · Answer 1 · answered Jan 23 '18 at 06:32

Using the and adapting the comment from undefined_variable the correct query looks like this:

SELECT value,COUNT(*) FROM
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t0.logdata, ',', n.n), ',', -1) value
  FROM t0 CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n 
   ) n
 WHERE n.n <= 1 + (LENGTH(t0.logdata) - LENGTH(REPLACE(t0.logdata, ',', '')))
 ORDER BY value) nt0 GROUP BY value

How to split string token and group by count them in mysql?

1 Answers1