Grouping MySql Array Rows

Question

I have table like this (value column values seperated as '@#@')

Id | Values
1 | abc@#@def@#@ghi@#@
2 | def@#@ghi@#@xyz@#@
3 | abc@#@def@#@xyz@#@

Now I want the result count as

abc = 2
def = 3
xyz = 2

How can i do this?

Answer 1

If you know the substring values to look for, it is rather easy.

select string,  
(
length(group_concat(values)) -- length of whole thing
-length(group_concat(replace(values, string, '')) -- length of whole thing without searched string
)-- substraction equals length of string*count of strings
/length(string) -- length of string
as string_count
from values 
cross join 
(select abc as string
union all select def ...)
group by 1

You might also have to expand your max group concat with

SET SESSION group_concat_max_len = 1000000;

If you do not have those values, I suggest you look into 'mysql explode', Can you split/explode a field in a MySQL query? and create a table with those values (distinct) beforehand

Answer 2

It can be done but a bit messy.

Firstly, generate a range of numbers that is larger than the number of possible values in your delimited field. Then cross join that against your table.

The basics to split it up would be as follows (assuming a max of 100 delimiteed values in this case):-

SELECT Id, SUBSTRING_INDEX(SUBSTRING_INDEX(sometable.`values`, '@#@', sub0.aNum), '@#@', -1)
FROM sometable
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON ((LENGTH(sometable.`values`) - LENGTH(REPLACE(sometable.`values`, '@#@', ''))) / 3) >= sub0.aNum;

This can then be expanded to get you the counts:-

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(sometable.`values`, '@#@', sub0.aNum), '@#@', -1) AS the_value, COUNT(sometable.id)
FROM sometable
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON ((LENGTH(sometable.`values`) - LENGTH(REPLACE(sometable.`values`, '@#@', ''))) / 3) >= sub0.aNum
GROUP BY the_value;

You could maybe simplify this if a delimited value can only occur once in any row:-

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(sometable.`values`, '@#@', sub0.aNum), '@#@', -1) AS the_value, COUNT(DISTINCT sometable.id)
FROM sometable
CROSS JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
GROUP BY the_value;

Another possibility would be if you had a table that contains a list of the values:-

SELECT possible_values.avalue AS the_value, COUNT(DISTINCT sometable.id)
FROM sometable
INNER JOIN possible_values
ON FIND_IN_SET(possible_values.avalue, REPLACE(sometable.`values`, '@#@', ','))
GROUP BY the_value;

However, in general the best situation is to avoid fields containing delimited values. They are a sign of a poorly normalised database.