SQL
Count Records Per Category with GROUP BY
Summarize data by grouping rows and applying aggregate functions like COUNT, useful for displaying counts of items in different categories or classifications.
SELECT
c.name AS category_name,
COUNT(p.id) AS total_posts
FROM
categories c
LEFT JOIN
posts p ON c.id = p.category_id
GROUP BY
c.name
ORDER BY
total_posts DESC;
How it works: This query counts the number of posts associated with each category. It uses a LEFT JOIN to ensure that all categories are included in the results, even if they currently have no posts. The GROUP BY clause aggregates the counts for each distinct category name, and the results are ordered to show categories with the most posts first.