How to do a MySQL Inner join combined with a COUNT and GROUP BY.

Assuming there are two tables with one having details about “posts” and the other “categories” each post has a category which is linked to the id in the category table.

Outputing all the category names and how many posts that have assigned that category is done with this query:

SELECT posts.category, COUNT(*) AS the_count, category.name
FROM posts
         INNER JOIN category
                    ON posts.category = category.id
GROUP BY category.name
ORDER BY the_count DESC

The most confusing aspect is that COUNT(*) isnt assigned to any table which is completely fine because it only counts rows that meet the requirements. These requirments are category that are the same (grouped).