CODEWITHSUNDEEP
sqlmysqlcount
Liam
Liam

Reputation: 4055

MySQL COUNT() multiple columns

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

Upvotes: 4

Views: 8079

Answers (3)

Carl Manaster
Carl Manaster

Reputation: 40336

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 that will do it, although there will be double-counting if any record has the same values for two of the tags.

UPDATE: added AS X.

Upvotes: 6

Eric
Eric

Reputation: 19152

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

Is it possible for you to change your schema? If so, I think it would be best if you normalized this by pulling the tags out into a separate table. In such a case, you might end up with 2 or 3 tables, depending on whether the tags can be arbitrary strings or are from a set/list. Under this set up, you'd have

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

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

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89661

You need to unpivot the 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 the flv is determined for a particular tag, since each id can have a single flv and up to 3 tags, it seems like any tag can have many different flv.

Upvotes: 4

Related Questions