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).