Below is the data set in the table:
Below is the desired output:id 4,1,44,2 1,2,3,4 3,6,1,2
id count 1 3 2 3 3 2 4 2 6 1 44 1
Below is the sql query that will bring the desired output:
SELECT val, COUNT(*) as cnt FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.val, ',', n.n), ',', -1) val FROM table t 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(t.val) - LENGTH(REPLACE(t.val, ',', ''))) and val <> '' ) T GROUP BY val ORDER BY cnt DESC