0

I have a table called 'users' and a table called 'posts'. posts table holds id information for users as a foreign key attribute. I want to select every username that corresponds to id number in posts table FROM users table. Then i will use COUNT(*) function and GROUP BY username statement to be able to get post count for every user. How can i do this?

Gimballock
  • 457
  • 1
  • 4
  • 8

1 Answers1

0

I wouldn't group by username, because there could be multiple users having the same name. Instead group by the user's id.

SELECT users.username, count(*)
  FROM users, posts
  WHERE users.id = posts.user_id
  GROUP BY users.id;
JSchirrmacher
  • 3,243
  • 2
  • 19
  • 27
  • If there are multiple users with the same name, then only including `username` in the resultset will not enable you to distinguish between them. – eggyal May 19 '16 at 18:42
  • yeah, but it is easy to add users.id, users.firstname (if such exists) or similar. The OP only wanted to select usernames, therefore I only included this field. – JSchirrmacher May 19 '16 at 18:44
  • username column is unique in my table. So there is no need to discuss that ^^ Thanks :) @eggyal – Gimballock May 19 '16 at 18:47
  • @JoachimSchirrmacher: I know. I was just continuing with the premise you stated in your answer "*there could be multiple users having the same name*"—if that were the case, your SQL would be problematic. Certainly adding `id` to the resultset would be a solution, I was just trying to point out the inconsistency. :) – eggyal May 19 '16 at 18:49