MySQL Counting matching rows using a JOIN statement

Question

I have two tables groups and users. I want to write a query that returns the rows from groups with an extra column that says how many rows in users have a gid that matches the id of the corresponding group. I have written a query that works fine, except in the case where there are no users in the users table with a corresponding gid, in which case that group is omitted from the result.

SELECT a.id, a.admin, a.name, COUNT(b.zbid) users FROM groups a LEFT JOIN users b ON b.gid=a.id

How can I get around this? I have worked out that it is definitely the COUNT that breaks the functionality. Why? Well if I try the following:

SELECT a.id, a.admin, a.name, IF(ISNULL(b.zbid), 0, 1) users FROM groups a LEFT JOIN users b ON b.gid=a.id WHERE a.cid='1'

then no rows are omitted, and it will show users=1 for any row with corresponding users, and 0 otherwise. However, if I change this to:

SELECT a.id, a.admin, a.name, IF(ISNULL(b.zbid), 0, COUNT(b.zbid)) users FROM groups a LEFT JOIN users b ON b.gid=a.id WHERE a.cid='1'

then it omits the rows again...

Any help would be appreciated.

Gordon Linoff · Accepted Answer · 2014-07-15 01:52:18Z

2

This is your first query:

SELECT a.id, a.admin, a.name, COUNT(b.zbid) as users
FROM groups a LEFT JOIN
     users b
ON b.gid = a.id;

You need to include a group by, such as group by a.id to get counts for all the groups.

I suspect the lack of group by is the problem you are facing.

answered Jul 15, 2014 at 1:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Counting matching rows using a JOIN statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related