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

Don't store data like that (separed values in one column)!!! It will only cause you lots of trouble. One value/row is the SQL way! — jarlh, Dec 01 '15 at 08:48
As Jarlh said above! However if you cannot change this, are they always separated by 3 ::: ? — Matt, Dec 01 '15 at 08:52
It's not my table. I don't have privilege to modify. Also there is tons of data already in. — Gereltod, Dec 01 '15 at 08:52

Matt · Accepted Answer · 2015-12-01T09:14:19.760

4

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

edited Dec 01 '15 at 09:14

answered Dec 01 '15 at 09:01

Matt

14,906
27
99
149

Thank you sir! Working exactly like what I wanted – Gereltod Dec 01 '15 at 09:16
The above code wont work properly if your data is something like this - aaa:::a:::dfsdfwerr:::dsfsdfsd:::werwe2334:::324a or just a single value like 'asdf'.. I would suggest you to follow what @Tim Biegeleisen has done with some corrections in it. So i'm posting an updated query as an answer below – Snm Dec 01 '15 at 20:52

Tim Biegeleisen · Answer 2 · 2015-12-01T09:29:03.113

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

edited Dec 01 '15 at 09:29

answered Dec 01 '15 at 08:55

Tim Biegeleisen

502,043
27
286
360

There is floating numbers in count result. I don't understand why you divide by 3. There is no fixed character count on each computer in computer field. – Gereltod Dec 01 '15 at 08:58
I think you are missing the point. Take the string `ersz:::dfwqq`. If the replaced version (removing the `:::`) has a length which is 3 _less than_ the original, then you count 1 (plus 1). Does this make sense? – Tim Biegeleisen Dec 01 '15 at 08:59
I see. Still there is float numbers. – Gereltod Dec 01 '15 at 09:00
Notice computer field can be like these. aaa:::a:::dfsdfwerr:::dsfsdfsd:::werwe2334:::324a or just sdf – Gereltod Dec 01 '15 at 09:01
Yes, your version almost worked. But when I run your query, there is always float numbers on count field. – Gereltod Dec 01 '15 at 09:21
No, my initial attempt had a problem because I didn't wrap the difference before dividing it. – Tim Biegeleisen Dec 01 '15 at 09:22

score 1 · Answer 3 · answered Dec 01 '15 at 20:54

1

SELECT product_id, model,
    CAST(SUM(1 + ((LENGTH(TRIM(BOTH ":::" FROM computers)) - LENGTH(REPLACE(TRIM(BOTH ":::" FROM computers), ":::", ""))) / 3)) AS UNSIGNED) AS cnt
FROM products
GROUP BY product_id, model

SQLFIDDLE

answered Dec 01 '15 at 20:54

Snm

435
4
15

Both of your codes working. I just tested your comment on answered post. I don't have data wihout ::: outside. All datas wrapped between :::. Upvoted your post for help someone who need different approach. – Gereltod Dec 02 '15 at 02:23

Mysql query explode and count

3 Answers3