JOINING MULTIPLE TABLES-Multiple Tables with Reddit Q5

Hello! I’m working at the question 5 of this link https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-reddit.

The exercise asks me to: "Use a LEFT JOIN with the users and posts tables to find out how many posts each user has made. Have the users table as the left table and order the data by the number of posts in descending order.

I used this query:

SELECT users.username, COUNT (posts.title)
FROM users
LEFT JOIN posts
ON users.id=posts.user_id
GROUP BY users.username
ORDER BY 2 DESC;

I noticed that the results are slightly different if I group by users.id, but I can’t understand why.
All the usernames and the ids are unique and each id is associated with one username only, so I expect the results to be the same. Do you know why it doesn’t happen?
Thanks!

except some of the usernames are null, if you select all the users:

SELECT * from users;

you will see some null values (user with id 14 for example), there are couple:

SELECT * FROM users where username is null;

If we group by username, the null values are grouped. Where as by ids, this is not the case

1 Like