How do you count rows per category including categories with zero rows?
Model answer
Use a LEFT JOIN from the categories table to the items table and COUNT a column from the right table: SELECT c.name, COUNT(i.id) FROM categories c LEFT JOIN items i ON i.category_id = c.id GROUP BY c.name. COUNT(i.id) ignores NULLs, so empty categories show 0, while COUNT(*) would show 1.
Learn this topic
GROUP BY and HAVING: Summaries for Each Group in the course SQL for Beginners: Learn Databases Step by Step.
Sign in to add your own answer.