I am needing a way to count comma separated values like this - any suggestions please?
Table:
id (int) | site (varchar)
1 | 1,2,3
2 | 2,3
3 | 1,3
Desired output:
site | # of occurrences
1 | 2
2 | 2
3 | 3
Asked
Active
Viewed 1,233 times
2
Yaakov Shoham
- 10,182
- 7
- 37
- 45
user2680315
- 113
- 8
-
1. Don't start with a comma-separated string. – Strawberry Oct 05 '16 at 15:16
-
or don't bother with an RDBMS – Strawberry Oct 05 '16 at 15:16
-
http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Oct 05 '16 at 15:17
1 Answers
0
Without getting into exactly what you're doing, I'll assume you have a sites table. If so, it's technically achievable with something like
SELECT sites.site_id AS site, COUNT(1) AS `# of occurrences`
FROM sites
INNER JOIN table ON FIND_IN_SET(sites.site_id, table.site)
GROUP BY sites.site_id
Performance of that will be appalling, as there is no way to use an index, and the data will be able to get inconsistent very easily.
What the comments in your question are alluding to, is to use a relational table of some description, where instead of storing a comma-separated list, you store a row for each 'occurrence'
calcinai
- 2,567
- 14
- 25