why this left join query failed to load all the data in left table ?

Question

users table

+-----+-----------+
| id  | username  |
+-----+-----------+
|   1 | tom       |
|   2 | jelly     |
|   3 | foo       |
|   4 | bar       |
+-----+-----------+

groups table

+----+---------+-----------------------------+
| id | user_id | title                       |
+----+---------+-----------------------------+
|  2 |       1 | title 1                     |
|  4 |       1 | title 2                     |
+----+---------+-----------------------------+

the query

SELECT users.username,users.id,count(groups.title) as group_count 
FROM users 
LEFT JOIN groups 
ON users.id = groups.user_id

result

+----------+----+-------------+
| username | id | group_count |
+----------+----+-------------+
| tom      |  1 |           2 |
+----------+----+-------------+

where is the rest users' info? the result is the same as inner join , shouldn't left join return all left table's data?

PS:I'm using mysql

Answer 1

What do you expect the group_count column to be, had all the users been returned? If you expected zero for users without groups, which would seem natural, how would it reach that result and still output at least one row?

Answer 2

Don't you need a group by clause?

group by users.username,users.id

not knowing much about mysql but in mssql you would need this for it to run