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.
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;
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