Denis Bobrovnikov Denis Bobrovnikov - 3 months ago 9
MySQL Question

SQL - How To Order Using Count From Another Table

1. Bloggers

blogger_id
1
2
3


2. Posts

post_from_blogger_id
1
1
1
2
2
3


As you can see blogger №1 posted more than the others and blogger №3 less. The question is
how to build a query that selects all bloggers and sorts them by the number of their posts?

Answer
 SELECT bloggers.*, COUNT(post_id) AS post_count
    FROM bloggers LEFT JOIN blogger_posts 
    ON bloggers.blogger_id = blogger_posts.blogger_id
    GROUP BY bloggers.blogger_id
    ORDER BY post_count

(Note: MySQL has special syntax that lets you GROUP BY without aggregating all values, it's intended for exactly this situation).

Comments