CODEWITHSUNDEEP
mysqlsql
Jérémie Chazelle
Jérémie Chazelle

Reputation: 1818

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;

Upvotes: 0

Views: 1705

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

If I understand correctly, you probably want something like this:

SELECT book, chapter,
       SUM(LENGTH(REPLACE(text, 'hello', 'hellox')) - LENGTH(text)) as cnt
FROM my_table
WHERE book = 43
GROUP BY book, chapter;

Notice that the COUNT() is much simpler -- no division. This replaces hello with a string one character longer and then subtracts the length of the original text.

Upvotes: 0

Blank
Blank

Reputation: 12378

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

Upvotes: 1

GrabNewTech
GrabNewTech

Reputation: 641

All column in the SELECT list should present in GROUP BY clause. So please add all SELECT list columns in GROUP BY clause (or) remove from SELECT list which is not in GROUP BY clause

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

Upvotes: 0

Related Questions