Getting most popular tags [closed]

Writing a query that returns like the table below. What I am trying to do is get the popularity of tags based on the results returned.

SELECT thing, tags from table WHERE field LIKE '%thing%';

+----+------------+------------------+
| id | thing      | tags             |
+----+------------+------------------+
| 1  | thing1     | tag1, tag2, tag3 |
| 2  | thing2     | tag1, tag4, tag5 |
| 3  | thatthing  | tag1, tag2, tag5 |
| 4  | thisthing  | tag3, tag4, tag5 |
+----+--------+----------------------+

Trying to write a query that will return tag popularity like this…

+---------------------+-------+
| tag                 | count |
+---------------------+-------+
| tag1                | 3     |
| tag5                | 3     |
| tag2                | 2     |
| tag3                | 2     |
| tag4                | 2     |
+---------------------+-------+

or even maybe just return from the initial query with just the tags and counts for each.

Answer

Your first effort should go into fixing your data model. Do not store multiple values in a delimited list.

You should have a separate table to store the relations between things and tags, with eac tuple on a different row.

Table things:

id     thing
-------------
1      thing1
2      thing2

Table tags:

id      tag
------------
1       tag1
2       tag2

Table things_tags:

thing_id    tag_id
------------------
1           1
1           2
2           2

Then, couting the popularity of each tag is as simple as:

select t.tag, count(*) cnt
from tags t
inner join things_tags tt on tt.tag_id = t.id
group by t.id
order by cnt desc

On the other hand, a design such as yours makes it much more complicated to maintain data integrity and manipulate the values. What you ask for would typically require a recursive query to split the csv strings into elements.

Recommended reading: Is storing a delimited list in a database column really that bad?.