SQL posts top tags

Question

I am using a MySQL database and looking to capture the top tags from my blog. The table looks like this:

++++++ post_tags ++++++
+ id INT(10)          +
+ post_id INT(10)     +
+ tag_id INT(10)      +
+++++++++++++++++++++++

++++++++ tags +++++++++
+ id VARCHAR(10)      +
+ title VARCHAR(50)   +
+ uri VARCHAR(75)     +
+++++++++++++++++++++++

I want to grab the top tags in the database by grouping the post_id from the post_tags table then grabbing the top 5 number of entries. It would look something like this:

Green              157
Water               92
Rocks               88
Purple              53
Sky                 44

Thank you in advance.

Answer 1

SELECT TOP 5 tags.title, COUNT(*) AS num_posts
FROM tags
INNER JOIN post_tags ON tags.id = post_tags.tag_id
GROUP BY tags.title
ORDER BY COUNT(*) DESC

This query will omit tags that have no posts. If you want to include them, use LEFT OUTER JOIN.