1

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
Improve this question
3
  • Is there a table Tags? Commented Jun 21, 2020 at 8:25
  • @forpas yes i have a table for tags.I have updated the details in question. Commented Jun 21, 2020 at 8:26
  • The column active in the table tags cries for a better name. When looking at it for the first time i thought this field was there to indicate if the tag as active or not active. Commented Jun 21, 2020 at 8:56

3 Answers 3

Reset to default
1

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       |
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

thanks for the answer. Let me check the space part and will let you know. Voted up.
There are no space in between the tags but thanks for keeping that in answer. It can help others. Just want to understand one thing. If tags name not present in posts but exist in tags field then what happen.
I want to show the count of tags those exist in posts table.
Thanks for the answer. Do you think it is a good practice? Or is there any better way to handling such scenarios.
Thanks i will read more about it. Your solution is working fine. I will raise new question if required.
|
1

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
;
Improve this answer

10 Comments

Thanks for the answer. I need to read it as its goes over my head. :)
If there is a Tag 'Working' the function instr() will count it in the tag 'Work' also.
Yes, but with FIND_IN_SET() you also have problems. Like: select find_in_set('City, Friend, Work','Work');, which returns 0. So one should always try to understand the usability of the function which is used.... 😁
Read about find_in_set() before you post: the 1st argument is the string to search and the 2nd is the list: select find_in_set('Work', 'City, Friend, Work');
Ok, the problem is that with INSTR the parameters are the other way around, but still select find_in_set('Work','City, Friend, Work'); returns 0.
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.