CODEWITHSUNDEEP
mysql
PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

How to count repeating data in mysql

id  | category
001 | 1000
001 | 300
002 | 500
003 | 200;300;100
004 | 100;300
005 | 200;3000

The result should be

Category | Total
1000     | 1
300      | 3
500      | 1
200      | 2
100      | 2

How can I arrive on that result? I saw something that I need to use find_in_set but its kind of complicated for me.

Any help on this will be greatly appreciated!

PS: I know the solution for this is to normalize but I guess it's a big work and I don't have an access to change database structure. So I guess if there's a solution to make a query work that will be great! :)

Thanks you!

Upvotes: 1

Views: 75

Answers (1)

Edgars Pavlovskis
Edgars Pavlovskis

Reputation: 23

Ok. my folt on previous answer! Below is a way to split a string by a delimiter in MySQL without using a stored procedure.

To use the method you will first need to have another table that has numbers from 1 up to however many choices each row can store. This table will be used in a join, so that the first choice will be joined to the row with number 1, the second choice to row 2, etc. So you would need a table like this: id 1 2 3 4 5 ...

Let's say your main table is called maintable with a category column, and your other table is called othertable with an id column (though you could use any table that had sequential numbers or id numbers).

this I used to create table for this exampe:

    CREATE TABLE maintable (id INT, category VARCHAR(255));
    INSERT INTO maintable VALUES (1, '1000'), (2, '300'), (3, '500'), 4, '200;300;100'), (4, '100;300'), (4, '200;3000');
    CREATE TABLE othertable (id INT);
    INSERT INTO othertable VALUES (1), (2), (3), (4), (5), (6), (7), (8);

this is mysql code:

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(maintable.category,';',othertable.id),';',-1) AS category,
COUNT(*) AS numtimes
    FROM maintable INNER JOIN othertable ON
(LENGTH(category)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(category,';',othertable.id),';',-1)
<> SUBSTRING_INDEX(SUBSTRING_INDEX(category,';',othertable.id-1),';', -1))
    GROUP BY category ORDER BY category;

and i got this resoult:

    category    numtimes
    100     2
    1000    1
    200     3
    200     2
    300     1
    500     1

Upvotes: 1

Related Questions