MySql COUNT(*) returns 1 instead of 0

Question

I have a sql-query to get the number of users of each group and sort all groups by them. But unfortunately, sql thinks that user_count is 1 when in fact it must be 0. If the number of users is greater than 0, it takes the correct value.

Here is my sql query:

SELECT G.id, COUNT(*) user_count
FROM groups G LEFT JOIN
     users U
     ON G.id = U.group_id
GROUP BY G.id
ORDER BY user_count

In groups table i have group ids in the field id and in users table i have users with their group ids in the field group_id

I'm not so good in SQL, so maybe i'm making some very stupid mistakes. Thank you in advance if you can help.

Gordon Linoff · Accepted Answer · 2018-02-16 14:56:11Z

5

COUNT(*) counts rows. You want to count matches, so count from the second table:

SELECT G.id, COUNT(u.group_id) as user_count
. . .

In other words, the LEFT JOIN returns a row when there is no match. That row has a NULL value for all the columns in the users table.

answered Feb 16, 2018 at 14:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Gordon Linoff Over a year ago

@SergeyShcherbakov . . . To a native English speaker, the question is pretty clearly about the 1 versus 0 in the count. The OP already has a query that does the other things. The question is about the values in the rows. As for your first comment, it is simply wrong. The OP simply needs to replace the COUNT(*) expression with the COUNT(u.group_id) in this answer.

Sergey Shcherbakov Over a year ago

@GordonLinoff Sorry, I see my mistake now.

Collectives™ on Stack Overflow

MySql COUNT(*) returns 1 instead of 0

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related