MySQL COUNT() multiple columns

Question

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

Carl Manaster · Answer 1 · 2010-04-12T21:52:42.013

6

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.

edited Apr 12 '10 at 21:52

answered Apr 12 '10 at 20:36

Carl Manaster

39,912
17
102
155

Unfortunately that gives me an error: "ERROR 1248 (42000): Every derived table must have its own alias" It's not something daft I'm forgetting is it? – Liam Newmarch Apr 12 '10 at 21:03
No - it's something daft *I'm* forgetting. Sorry, in mysql you need to name the inner select. I just toss "as x" on the end of it; will edit to demonstrate. – Carl Manaster Apr 12 '10 at 21:52

score 4 · Accepted Answer · answered Apr 12 '10 at 20:37

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.

score 4 · Answer 3 · answered Apr 12 '10 at 20:39

4

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.

answered Apr 12 '10 at 20:39

Eric

18,512
4
29
34

1

I agree with this **if** you expect to perform this query and others like it frequently. It's a more denormalized form, but in some use cases it *may* not be worth the effort. You should definitely weigh it as an option. – keithjgrant Apr 12 '10 at 20:42
I agree with Eric. Today you only want to have up to 3 tags per video. Next year you may decide you want 5. Every time I've done what the OP did (field1, field2, field3) for the sake of convenience it has always come back to bite me. Yesterday's iron clad rule becomes tomorrow's burdensome convention. – Autodidact Jun 21 '10 at 20:42

MySQL COUNT() multiple columns

3 Answers3

Linked