MySQL COUNT() multiple columns

Posted by liam on Stack Overflow See other posts from Stack Overflow or by liam
Published on 2010-04-12T20:29:42Z Indexed on 2010/04/12 20:33 UTC
Read the original article Hit count: 193

Filed under:
|
|

Hello, I'm trying to fetch the most popular tags from all videos in my database (ignoring blank tags). I also need the 'flv' for each tag. I have this working as 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?

The record structure is:

+-----------------+--------------+------+-----+---------+----------------+ 
| 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    |                | 
+-----------------+--------------+------+-----+---------+----------------+ 

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql