SQL query: get tag id and number of tag occurences

Question

Hi,

I think, the answer to my question is rather simple, but I just can't figure it out at the moment. I experimented with DISTINCT() and COUNT(), but had no success, although I know, that I did it somehow once before. So:

I have three tables: article, tag, and article_tag. The last one is simply two columns with article_id and tag_id building a unique key.

Now I want a single SELECT statement that delivers me the tag_id and a count of how many times it appears in the article_tag table. Something like

| tag_id  | count   |
+---------+---------+
| 1       | 14      |
| 2       | 3       |
| 3       | 34      |
+---------+---------+

from a query like:

SELECT tag_id, COUNT(something) AS count 
  FROM article_tag

Could someone tell me, how I could get this result? I guess, I should stop coding when it's tending towards 0:00 o'clock...

Answer 1

  SELECT tag_id, 
         COUNT(article_id) AS article_count 
    FROM article_tags 
GROUP BY tag_id

Answer 2

Try: select tag_id, count(article_id) from article_tag group by tag_id

Answer 3

select tag_id, count(*) from article_tag group by tag_id order by tag_id;

You can fool around with the order by as well. To see which tags have the most references:

  select tag_id, count(*) from article_tag group by tag_id order by count(*);