CODEWITHSUNDEEP
phpmysqlsql
Shivam
Shivam

Reputation: 2132

Group By Count in MySql (Users and Group)

I'm creating a social mobile-application which has groups and groups can have user. I'm trying to write a query in order to get all the groups along with number of users in it.

Note: Group can have 0 users as well.

Even if group has zero user, I still need to get its information. How should I do that? I tried:

Select *, count(ug.group_id) from groups g 
left join images i ON(g.group_image_id = i.image_id)
left join location l ON(g.group_location_id = l.location_id)
.
.
left join user_group ug on(ug.gorup_id = g.group_id)
group by ug.group_id;

Now, this query does not give me group that has zero user(s). How can change it so it shows all group even if group has 0 users.

Upvotes: 0

Views: 256

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

Using SELECT * and GROUP BY is a MySQL abomination. In your case, it doesn't look like it hurts since image and location appear to have a 0:1 relation ship with group. It's just very bad practice.

Here's an ANSI compliant way to write your query. If you want the full result set from JOINing a few tables, plus the count, then add only the count portion as an expression.

Select *, (select count(ug.group_id)
           from user_group ug
           where ug.gorup_id = g.group_id) GroupUserCount
from groups g 
left join images i ON(g.group_image_id = i.image_id)
left join location l ON(g.group_location_id = l.location_id)
.
.

Upvotes: 1

Benos
Benos

Reputation: 696

Your are grouping by a column of your left join, this means that your are grouping by null when a group have no users.

You need to group by id of the groups table and the count(*) will count the number of joined rows.

Then add the empty groups using a left join and where join row is null

Select g.group_id as groupId, count(*) as nb
    from groups g 
       join user_group ug on (ug.group_id = g.group_id)
    group by g.group_id
UNION ALL
    Select g.group_id as groupId, 0 as nb
    from groups g 
       left join user_group ug on (ug.group_id = g.group_id)
where ug.group_id is null
    group by g.group_id

[EDIT] One query count looking for null join in the select statement and setting nb to 0 if found

Select g.group_id as groupId, IF(ug.group_id is null, 0,count(*)) as nb
    from groups g 
       left join user_group ug on (ug.group_id = g.group_id)
    group by g.group_id

Upvotes: 0

Jose Areas
Jose Areas

Reputation: 717

I believe you are looking for something like:

Select g.group_id,coalesce(users ,0)
from groups g 
  left join (select ug.gorup_id,count(*) users
from user_group ug
group by ug.gorup_id) s
on s.group_id = g.group_id

If you only need to know the group and number of users, the other table in the example are not necessary.

Upvotes: 0

John Woo
John Woo

Reputation: 263943

you should group them from groups table

GROUP BY g.group_id

Upvotes: 1

Related Questions