Mysql query explode and count

Question

I have table like this:

product_id       model        date                  computers
----------------------------------------------------------------
12204            Asus         'random_date'         :::aass:::ddff:::ddfd:::dfwr:::
12205            Acer         'random_date'         :::ersz:::dfwqq:::bbs:::
12205            Acer         'random_date'         :::ettww:::iwoeur:::iwerq:::

As you see, product_id, model can duplicate. Computers field contains how much computer in current row. Separator is :::. After I group them (GROUP BY product_id) first product (12204) have 3 computer in it, and other product (12205) have 6 computers in it. So query result must be like this:

GROUP(product_id)      model       count
-----------------------------------------
12204                  Asus        4
12205                  Acer        6 

Is it possible do it with single query? Or I must do it at back end code (in my case python)?

(I can't modify table, i'm working on someone else's pre-existed table)

Edit: updated sample data format

Answer 1

Using a combination of SUM, ROUND, LENGTH, REPLACE & COUNT for the count field, and then group by the product_id and model.

SELECT product_id, model, 
SUM(ROUND ((LENGTH(computers) - LENGTH(REPLACE(computers, ":::", ""))) / LENGTH(":::"))) - count(product_id) AS count
FROM yourtable
GROUP BY product_id, model

Output

product_id  model   count
12204       Asus    4
12205       Acer    6

SQL Fiddle:http://sqlfiddle.com/#!9/ad183/2/0

Answer 2

Try the following query:

SELECT product_id, model,
    SUM(1 + ((LENGTH(computers) - LENGTH(REPLACE(computers, ':::', ''))) / 3)) AS count
FROM products
GROUP BY product_id, model

Click the link below for a running demo:

SQLFiddle