CODEWITHSUNDEEP
sqlphpmyadmin
Jack Logan
Jack Logan

Reputation: 95

Count number of posts of each user - SQL

I need to get the number of posts each user has created. This is the structure of both tables (users, microposts).

Microposts

Users

SELECT users.*, count( microposts.user_id ) 
FROM microposts LEFT JOIN users ON users.id=microposts.user_id 
GROUP BY microposts.user_id

This gets me only the users that have posts. I need to get all users, even if they have 0 posts

Upvotes: 2

Views: 974

Answers (1)

MatBailie
MatBailie

Reputation: 86706

You have the join in the wrong order.

In a LEFT JOIN you ensure you keep all the records in the table written first (to the left).

So, join in the other order (users first/left), and then group by the user table's id, and not the microposts table's user_id...

SELECT users.*, count( microposts.user_id ) 
FROM users LEFT JOIN microposts ON users.id=microposts.user_id 
GROUP BY users.id

Upvotes: 1

Related Questions