Find and count occurences of a substring of table1 in table2 and have a result group by another column of table2

I have a table1

| id | mystring
|1   |value1-value2-value4|
|2   |value2-value3       |
|3   |value3-value4-value5|

than the table2

| id |type  | name | cat|
|1   |type1 |value1|cat1|
|2   |type1 |value2|cat1|
|3   |type1 |value3|cat1|
|4   |type1 |value4|cat2|
|5   |type2 |value5|cat3|

then I want this result

|cat |count|
|cat1|5    |
|cat2|2    |

I search a lot on and the best I manage to do is

SELECT cat,name,
      (SELECT ROUND((SUM(CHAR_LENGTH(mystring)) -
                SUM(CHAR_LENGTH(REPLACE(mystring, name, ''))))
               / CHAR_LENGTH(name)) FROM table1 AS occurrence_count
FROM table2 GROUP BY name,cat having occurrence_count>0

but the result is

|name  |cat |count|
|value1|cat1|1    |
|value2|cat1|2    |
|value3|cat1|2    |
|value4|cat2|2    |

But I want it to be group by cat only and have the sum of all the values in the same categories.
Ih I delete “name” in the GROUP BY, The count is false because the result just forget the 2 last rows. So please can anybody help me ?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I would probably write this query using a join approach:

SELECT, COUNT( AS occurrence_count
FROM table2 t2
LEFT JOIN table1 t1
    ON CONCAT('-', t1.mystring, '-') LIKE CONCAT('%-',, '-%')


All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x