CODEWITHSUNDEEP
phpmysql
user1047813
user1047813

Reputation: 67

Mysql left-join, count by unique value in a field not working

I have a table like below.

I want to be able to get a count of group_id then group by group_id then left join groups table where groups_user.group_id = groups.id but I'm only getting one result back.

I want group by unique group_id then count of each duplicate group_id. So far my query is like below:

SELECT 'groups.group' ,COUNT('groups_users.group_id') as groups_count
FROM `groups_users`
LEFT JOIN groups
ON 'groups_users.group_id' = 'groups.id'
GROUP BY 'groups_users.group_id'

Table:

id  group_id    user_id
26  3           1
22  2           1
19  1           1
20  1           2
21  1           4

Where am I getting wrong here?

Upvotes: 0

Views: 368

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135838

I think you've got the JOIN backwards. Try:

SELECT g.group, COUNT(gu.group_id) AS groups_count
    FROM groups g
        LEFT JOIN groups_users gu
             ON g.id = gu.group_id
    GROUP BY g.group;

Upvotes: 1

Related Questions