The mysql query explodes and counts
source link: https://www.codesd.com/item/the-mysql-query-explodes-and-counts.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
The mysql query explodes and counts
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
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK