Reputation: 82754
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...
Upvotes: 1
Views: 305
Reputation: 748
SELECT tag_id,
COUNT(article_id) AS article_count
FROM article_tags
GROUP BY tag_id
Upvotes: 4
Reputation: 39495
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(*);
Upvotes: 3
Reputation: 6640
Try: select tag_id, count(article_id) from article_tag group by tag_id
Upvotes: 3