-1

How can I select the distinct values and relative count from data that is stored as comma separated values in MySql?

This is how the data in the table looks like:

"Category"    "Values"    
"red"         "D,A,D,D"    
"yellow"      "A,A,D,D"

Desire Output:

"red"    "D" "3"    
"red"    "A" "1"    
"yellow" "D" "2"    
"yellow" "A" "2"

Can you think of a good way in mysql to get output like the way I've done? Thank's in advance.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Gabriele Garufi
  • 1
  • 1
  • 2
    Yes the good way I can think of is to normalize the table and make it one-to-many and life will become simple afterwards. – Abhik Chakraborty Nov 15 '16 at 16:07
  • http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Nov 15 '16 at 16:24

1 Answers1

3

Using a derived table you can split the string into rows

SELECT `Category`, SUBSTRING_INDEX(SUBSTRING_INDEX(t.`Values`, ',', n.n), ',', -1) `value`
FROM table1 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.`Values`) - LENGTH(REPLACE(t.`Values`, ',', '')))

Then just GROUP BY and COUNT

DEMO

SELECT `Category`, `value`, COUNT(*) as cnt
FROM (
    <previous query>
   ) T
GROUP BY  `Category`, `value`

enter image description here

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • this will work well as long as you know the upper range of possible values, in this case 9. very nice solution Juan Carlos. – T Gray Nov 15 '16 at 16:27