count multiple row values separated by comma

Question

I have a MySQL DB with multiple rows and a column that contain some text, separated with comma.

For a quick idea, my DB look like this:

id | column
 1 |  aaa, bbb
 2 |  ccc, ddd, aaa
 3 |  eee, aaa, ccc
 4 |  ddd

Output need to be like this:

aaa - 3
bbb - 1
ccc - 2
ddd - 2
eee - 1

After I search all over the internet, I still don't know how to do it. Please, be kind and give me some ideas to solve it.

Answer 1

Assuming you have a reason for your data structure and you are not willing you normalize the database then you will need to do this in PHP, however I strongly recommend normalization if there is not a reason.

// This will come from the query, but for the example
$array = Array("aaa, bbb", "ccc, ddd, aaa", "eee, aaa, ccc", "ddd");

// Initilize an empty array for holding count values
$counterArray = Array();

// Loop through each "table row"
foreach ($array as $row)
{
    // Split the row on the comma
    $splitRow = explode(',', $row);

    // Loop through each item found on the row
    foreach ($splitRow as $item)
    {      
        // Trim to get rid of any leading/trailing spaces
        $arrayKey = trim($item);

        // Check if the array contains the values already, if yes increment otherwise creaet a record with a initial value of 1
        if (!array_key_exists($arrayKey, $counterArray))
            $counterArray[$arrayKey] = 1;
        else
            $counterArray[$arrayKey]++;
    }
}

Answer 2

// This will come from the query, but for the example
$array = Array("aaa, bbb", "ccc, ddd, aaa", "eee, aaa, ccc", "ddd");
print_r(array_count_values($array));

// you will get associative array with count.

Answer 3

It is no-brainer that Normalizing your database is the best solution here. Also, as the other answers suggested, achieving the mentioned task programmaticaly will be a simple and efficient solution, too.

But if the above 2 options are not suitable for you at all, then following is the way to achieve it with PL\SQL.

1) If you only want to find the Number of occurrence of a single word at a time, you can try following way:

select
distinct
'aaa' as Word,
ROUND (   
        (
            CHAR_LENGTH(@str)
            - CHAR_LENGTH( REPLACE (@str, 'aaa', "") ) //you can put the word here you want
        ) / CHAR_LENGTH('aaa')        
    ) AS count
    from t
   ;

Result:

    word | count
   --------------
    aaa  |  3

2) If you want to display the count of all the words as given in question, you need a Function, a Procedure and a Temp Table.

Step 1: Create an Temporary table.

         create table temp(str varchar(10));

Step 2: Create a Function to split data with delimiter , with given position.

CREATE FUNCTION SPLIT_STR
(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Now, the above function will split data only for the given position. In order to split whole string, we need to iterate this function. That's our step 3.

Step 3: Create Procedure to iterate the above function;

CREATE PROCEDURE ABC(IN fullstr varchar(1000))
   BEGIN
      DECLARE a INT Default 0;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,",",a);         
         IF str='' THEN
            LEAVE simple_loop;       
         END IF;        
         #Do Inserts into temp table here with str going into the row
         insert into t1 values (str);
   END LOOP simple_loop;

Step 4: Store whole column into one variable:

select @str:= group_concat(columnname separator ',') from t;

Step 5: Call Procedure:

       call abc(@str);

Step 6: Get count from temp table:

select
str as word,
count(*) as occurence   
from temp
group by str;

Results:

        word  | occurence
   -------------------------
    1   aaa   |    3
    2   bbb   |    1
    3   ccc   |    2
    4   ddd   |    2
    5   eee   |    1

Click here for the DEMO

Hope it helps!

Answer 4

Thank you, guys.

Based on your ideas, I made a poorly script that do the job, without modify DB. My DB is already normalized, except one single column of one table(witch was not designed to be used like in my example). I will describe the script here, for further user who will need it.

My column can only contain between 0-5 predefined words, comma separated.
For each word, I assigned a $var with his name. Then, in while loop from MySQL, i check if string contain that word.
if(strpos($domeniu,'Producator') !== false){$producator = $producator + $count_domeniu;}.
Repeat code for every word.
Producator is one word from that 5, predifined earlyer.
$producator is $var defined 0, before while loop.
$count_domeniu = $row['count_domeniu'];
After the While loop, echo $producator var.

My mysql query is ("SELECT domeniu_activitate, COUNT(domeniu_activitate) as count_domeniu FROM tbl_expozanti GROUP BY domeniu_activitate").

Answer 5

It's an old question but while going through the same problem, I landed here also while searching for answers so others may also. Finally found the perfect answer for myself that will work for you too in a simple MySQL query way. So here is the simple query to count multiple row values separated by a comma.

As per your table, the data is structured somehow as...

id | column
 1 |  aaa, bbb
 2 |  ccc, ddd, aaa
 3 |  eee, aaa, ccc
 4 |  ddd

So to get your answer, just use the following query...

SELECT substring_index(column, ',', -1)  AS columnTextRenamed, COUNT(substring_index(column, ',', -1)) AS columnCountRenamed
FROM my_table_name
GROUP BY columnTextRenamed
ORDER BY columnTextRenamed;

And then this will output as...

columnTextRenamed | columnCountRenamed
aaa               |                  3
bbb               |                  1
ccc               |                  2
ddd               |                  2
eee               |                  1

And this is what you want.