MySQL: Get count of data in comma separated column

Question

I have a table that stores data like:

userid    books
ym0001    dictionary,textbooks,notebooks
ym0002    textbooks,dictionary

I want to count number of times each book occurs. I want my result to be in this format.

books       Counts
dictionary  2
notebooks   1
textbooks   2

This is mysql. Please help

Answer 1

The following approach builds a result of 1000 integers, then uses those integers (n) to locate segments within the comma seperated string, and for each segment it creates a new row so that the derived table looks like this:

userid | book      
:----- | :---------
ym0001 | dictionary
ym0002 | textbooks 
ym0001 | textbooks 
ym0002 | dictionary
ym0001 | notebooks 

Once that exists it is a simple matter of grouping by book to arrive at the counts.

select
    book, count(*) Counts
from (
    select
           t.userid
         , SUBSTRING_INDEX(SUBSTRING_INDEX(t.books, ',', numbers.n), ',', -1) book
    from (
        select @rownum:=@rownum+1 AS n
        from
        (
           select 0 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
        cross join (
           select 0 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
        cross join  (
           select 0 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
        ) c
        cross join (select @rownum:=0) r
        ) numbers
    inner join mytable t
      on CHAR_LENGTH(t.books)
        -CHAR_LENGTH(REPLACE(t.books, ',', '')) >= numbers.n-1
    ) d
group by
    book
order by
    book

book       | Counts
:--------- | -----:
dictionary |      2
notebooks  |      1
textbooks  |      2

1. If you already have a table of numbers, use that instead.
2. the cross joins of a b and c dynamically produce 1000 rows, if you need more add further cross joins similar to c. i.e. the number of numbers should exceed the maximum length of your comma seperated data

db<>fiddle here