-2

My MySQL table having column with comma separated numbers. See below example -

| style_ids  |
| ---------- |
| 5,3,10,2,7 |
| 1,5,12,9   |
| 6,3,5,9,4  |
| 8,3,5,7,12 |
| 7,4,9,3,5  |

So my expected result should have top 5 numbers with maximum appearance count in descending order as 5 rows as below -

| number | appearance_count_in_all_rows | 
| -------|----------------------------- |
| 5      | 5                            |
| 3      | 4                            |
| 9      | 3                            |
| 7      | 2                            |
| 4      | 2                            |

Is it possible to get above result by MySQL query ?

Ganesh Gadge
  • 366
  • 2
  • 17
  • 4
    Storing CSV data like in a MySQL database is usually a bad design decision. – Tim Biegeleisen Jan 17 '23 at 14:50
  • 1
    Another day with another [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) problem – Ergest Basha Jan 17 '23 at 14:51
  • 1
    *Is it possible to get above result by MySQL query ?*, if possible try and fix the schema – Ergest Basha Jan 17 '23 at 14:51
  • 1
    See the answers to the following SO question why it is a bad idea to store your data like you do: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad. Since mysql does not have intrinsic array type, it may be better if you extract the data into an application code and do the counting there. – Shadow Jan 17 '23 at 14:52

2 Answers2

2

As already alluded to in the comments, this is a really bad idea. But here is one way of doing it -

WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 20
), tbl (style_ids) AS (
    SELECT '5,3,10,2,7' UNION ALL
    SELECT '1,5,12,9' UNION ALL
    SELECT '6,3,5,9,4' UNION ALL
    SELECT '8,3,5,7,12' UNION ALL
    SELECT '7,4,9,3,5'
)
SELECT seq.n, COUNT(*) appearance_count_in_all_rows
FROM seq
JOIN tbl ON FIND_IN_SET(seq.n, tbl.style_ids)
GROUP BY seq.n
ORDER BY appearance_count_in_all_rows DESC
LIMIT 5;

Just replace the tbl cte with your table.

user1191247
  • 10,808
  • 2
  • 22
  • 32
2

As already pointed out you should fix the data if possible.

For further details read Is storing a delimited list in a database column really that bad?.

You could use below answer which is well explained here and a working fiddle can be found here.

Try,

select distinct_nr,count(distinct_nr) as appearance_count_in_all_rows 
from   ( select substring_index(substring_index(style_ids, ',', n),  ',',  -1) as distinct_nr
         from test
         join numbers on char_length(style_ids) - char_length(replace(style_ids, ',', ''))  >= n - 1 
        ) x
group by distinct_nr
order by appearance_count_in_all_rows desc  ;
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28