MySQL COUNT () multiple columns

Question

I am trying to extract the most popular tags from all the videos in my database (ignoring empty tags). I also need a "flv" for each tag. I work the way I want if each video has one tag:

SELECT tag_1, flv, COUNT(tag_1) AS tagcount FROM videos WHERE NOT tag_1='' GROUP BY tag_1 ORDER BY tagcount DESC LIMIT 0, 10 

However, in my database, each video is allowed three tags - tag_1, tag_2 and tag_3. Is there a way to get the most popular tags reading from multiple columns?

Record Structure:

 +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | flv | varchar(150) | YES | | NULL | | | tag_1 | varchar(75) | YES | | NULL | | | tag_2 | varchar(75) | YES | | NULL | | | tag_3 | varchar(75) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 

Answer 1

You need to disable data:

 SELECT tag, COUNT(*) FROM ( SELECT tag_1 AS tag UNION ALL SELECT tag_2 AS tag UNION ALL SELECT tag_3 AS tag ) AS X (tag) GROUP BY tag ORDER BY COUNT(*) DESC 

I'm not sure how FLV is defined for a specific tag, since each identifier can have one flank and up to three tags, it seems that any tag can have many different flvs.

Answer 2

 select tag, flv, count(*) as tag_count from ( select tag_1 as tag, flv from videos UNION select tag_2 as tag, flv from videos UNION select tag_3 as tag, flv from videos ) AS X 

I think this will be done, although there will be a double count if any record has the same value for two of the tags.

UPDATE : added AS X.

Answer 3

This is not exactly the answer to your question, but I believe that this is the best solution to your problem.

Is it possible to change the circuit? If so, I think it would be better if you normalized it by pulling the tags into a separate table. In this case, you can get 2 or 3 tables, depending on whether the tags can be arbitrary rows or from a set / list. Under this setting you will have

 Videos (VideoId, Flv) Tags (TagId, TagName) VideoTags(TagId, VideoId) 

Then it becomes quite easy to find the most popular tags.