Many-to-one relationship to implement tags: How to count how many times a tag is used?

Question

Hello,

I'm experimenting with a tagging system which is a many-to-one relationship. My schema is:

items table:

• item_id
• comment

comment _ tags:

• item_id
• tag_id

tags table:

• tag_id
• tag_name

I've been reading of implementation designs at the links at the bottom but got stuck. I can insert tags without a problem.

How do I fetch every tag that has been used and get how many times it has been applied to an item in the comment _ tags table?

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

http://stackoverflow.com/questions/388687/sql-query-for-product-tag-relationship

Answer 1

SELECT t.tag_name, COUNT(*)
FROM tags AS t
    INNER JOIN comment_tags AS c_t ON c_t.tag_id = t.tag_id
GROUP BY c_t.tag_id
ORDER BY t.tag_name;

Answer 2

not the correct names, but mostly correct syntex

select TagName, count(TagName)
from TagTable
group by TagName