SQL
Count Related Records for Each Parent Item
Discover how to count associated items (e.g., posts per user, orders per customer) by combining SQL LEFT JOIN and GROUP BY with COUNT().
SELECT u.id, u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;
How it works: This query counts the number of posts for each user. It uses a `LEFT JOIN` to ensure all users are included, even those without posts (which will have a `post_count` of 0). `GROUP BY` aggregates the results by user, and `COUNT(p.id)` tallies the posts for each group, providing a count of related items.