CODEWITHSUNDEEP
mysql
Anuj Gakhar
Anuj Gakhar

Reputation: 691

MySQL Count query with Group By not returning anything

I am trying to understand why this query in MySQL does not return any result at all whereas I think it should be returning one row with 0 as the column value

select count(ps.id) as totalcount 
from tblpresi ps
inner join users_staging.tbluser u on u.id = ps.userid
where ps.id = 3678
group by ps.id

The same query without the "Group By" works as expected.

Any ideas?

Upvotes: 0

Views: 94

Answers (2)

Blorgbeard
Blorgbeard

Reputation: 103467

Since you say you expect 1 row with zero count, I assume there is no ps.id of 3678.

If you group-by on an empty result-set, there can be no groups. The group by doesn't "know" about the where-clause, it's only looking through the result-set.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

No, it won't return that, since your query does not returns results at all (probably there is no ps.id = 3678, or your inner join rules out every row in the result set).

If you change your query to:

select count(1) as totalcount 
  from tblpresi ps
 inner join users_staging.tbluser u on u.id = ps.userid
 where ps.id = 3678
 group by ps.id

You will get one row with 0.

Do keep in mind that if your ps.id is unique in the result set (I don't know if that inner join you have will return multiple rows with the same ps.id) your group by clause is not necessary.

Upvotes: 2

Related Questions