SQL - How To Order Using Count From Another Table
Use subqueries.
select * from (
select post_from_blogger_id, count(1) N from Posts
group by post_from_blogger_id) t
order by N desc
Try this:
SELECT B.blogger_id,
B.blogger_name,
IFNULL(COUNT(P.post_from_blogger_id ),0) AS NumPosts
From Blogger AS B
LEFT JOIN Posts AS P ON P.post_from_blogger_id = B.blogger_id
GROUP BY B.blogger_id, B.blogger_name
ORDER BY COUNT(P.post_from_blogger_id ) DESC
This joins the 2 tables, and counts the number of entries in the Posts
table. If there are none, then the count is 0 (IFNULL
).
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).