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.
Advertisement
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?.