SQL
Count Related Records with JOIN and GROUP BY
Discover how to count associated records (e.g., comments per post) by joining two tables and using GROUP BY with an aggregate function.
SELECT p.id, p.title, COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, p.title
ORDER BY comment_count DESC;
How it works: This query joins the `posts` table with the `comments` table to count how many comments each post has. The `LEFT JOIN` ensures all posts are included, even those with no comments. `GROUP BY` aggregates the counts for each post, and `COUNT(c.id)` tallies the comments, providing a summary of related data.