How to select the distinct values and relative count along with sorting from data that is stored as comma separated values in MySql?

 

Below is the data set in the table:

id
4,1,44,2
1,2,3,4
3,6,1,2

Below is the desired output:

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

 

---

Related #tags:     #mysql     #sql    

---

Related questions

Looking for change? Click below to prepare for technical interview.

Technical Interview Questions