SQL: Get the count of related records

Micro tutorial:

Sometimes you need to show results from table A but also include the count of related records from table B. For example: how many items are in each purchase order, how many comments in each post, how many galaxies in each universe (wait, what?), …

The following query shows one way of selecting the title and id from a table Posts, the count of the related records in a table Comments and then sorts descending by that count. fk_post is the foreign key of the post in the Comments table.

SELECT
    Posts.id,
    Posts.title,
    (
        SELECT
            COUNT(*)
        FROM
            Comments
        WHERE
            fk_post=Posts.id
    ) as cnt
FROM
    Posts
ORDER BY
    cnt DESC

As always, there are multiple ways of doing it. So if you are not going to add more to the subquery, you might prefer to just use a LEFT JOIN with a GROUP BY:

SELECT
    Posts.id,
    Posts.title,
    Count(Comments.id) as cnt
FROM
    Posts
LEFT JOIN
    Comments
ON
    Posts.id=Comments.fk_post
GROUP BY
    Posts.id
ORDER BY
    cnt DESC