Q:

Count occurrences of character in a string and Group By with SQL

I try to count occurrences and to add group by after, but I have an error

SELECT list is not in GROUP BY clause and contains nonaggregated
  column ‘bdd.my_table.text' which is not functionally dependent on
  columns in GROUP BY clause; this is incompatible with
  sql_mode=only_full_group_by

My table :
id | book | chapter | text
 1 |   1  |    1    | 'hello hello world'
 2 |   1  |    2    | 'hello hello hello hello'
 3 |   1  |    3    | 'world'
 4 |   1  |    4    | 'hello test'

I execute my request :
SELECT 
    book,
    chapter,
    text,    
    ROUND (   
        (
            LENGTH(text)
            - LENGTH( REPLACE ( text, "hello", "") ) 
        ) / LENGTH("hello")        
    ) AS count    
FROM my_table
WHERE book=43
GROUP BY chapter

I would like this result :
book | chapter | count
----------------------
  43  |    1    |   2
  43  |    2    |   4
  43  |    3    |   0
  43  |    4    |   1

I tried to add this before the request to remove 'only_full_group_by', but I have the same problem
SET SESSION group_concat_max_len = 1000000;

A:

Try this, even I still don't know what you exactly want to do till now, but try it.
SELECT
    book,
    chapter,
    group_concat(text) as text,    
    ROUND (   
        (
            LENGTH(group_concat(text))
            - LENGTH( REPLACE ( group_concat(text), "hello", "") ) 
        ) / LENGTH("hello")        
    ) AS `count`
FROM my_table
GROUP BY chapter, book

