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 ?

Contents hide

Answers:

Method 1

Demo

Answers:

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 t2.cat, COUNT(t1.id) AS occurrence_count
FROM table2 t2
LEFT JOIN table1 t1
    ON CONCAT('-', t1.mystring, '-') LIKE CONCAT('%-', t2.name, '-%')
GROUP BY t2.cat;

Demo

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

0 0 votes

Article Rating