CODEWITHSUNDEEP
mysql
Roxx
Roxx

Reputation: 3986

MySQL: count comma separated values from field

This might sound little stupid but i don't have much experience in Mysql and database related things.

I have a table named posts and it has a field (Tags). Below is the sample data.

ID, Title,           Tags
1   Friend Title       Friend, School, City
2   College            College, City
3   Work               Work, City, Friend

Tags are comma separated.

I need to get the count of these tags. I am Expecting

School - 1
City - 3
College - 1
Work - 1

something like this.

I know before asking the question we need to show some code or query but i don't know where to start. I am not looking for complete solution.

Just guide me what to use then i will try.

Edit As advised by Forpas i have anohter table which contains the name of the tags. That looks like below.

Tagid  Tag      active
1      School    0
2      College   0
3      City      0
4      Home      0
5      Hotel     0

Upvotes: 1

Views: 729

Answers (3)

forpas
forpas

Reputation: 164089

Join the tables and aggregate:

select t.tag, count(p.id) counter
from tags t inner join posts p
on find_in_set(t.tag, p.tags)
group by t.tag

The function find_in_set() used in the ON clause will work if there are no spaces after the commas in the column tags of the table posts. If there are spaces then replace() must be used to remove them.

See the demo.
Results:

| tag     | counter |
| ------- | ------- |
| City    | 3       |
| College | 1       |
| School  | 1       |
| Work    | 1       |

Upvotes: 1

Luuk
Luuk

Reputation: 14920

Using WITH, works only in Mysql8.0+:

with tags as (
 select 'School' as tag union 
 select 'City' union 
 select 'College' union 
 select 'Work') 
select tag, count(*) from (
  select tag 
  from tags
  left join posts on instr(posts.tags,tags.tag)>0
) x
group by tag
;

Upvotes: 1

Oliver Hnat
Oliver Hnat

Reputation: 793

Depending on how many tags do you have, if you have only a few, you could just make an integer variable for each one of them and then make a bunch of if statements, that would add 1 to the variable that has the name of the tag.

Upvotes: 0

Related Questions