Empty group by with count returns null

Question

I have a query with a count/group by - If no person with the name 'Bob' is found then it returns no rows at all because the GROUP BY is on an empty set - null is returned for the count - I'd like to change the behaviour so that count returns 0 and not null.

     select p.Id as personId, count(*) as alias1 from
     Table1 as alias2
     join Table2 as alias3 on alias3.personId = alias1.Id
     where Name = 'Bob'
     group by p.Id

You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again. — pmbAustin, Nov 29 '18 at 00:08
Possible duplicate of [Count Returning blank instead of 0](https://stackoverflow.com/questions/19221630/count-returning-blank-instead-of-0) — Jared C, Nov 29 '18 at 00:13

score 1 · Answer 1 · answered Nov 29 '18 at 00:08

1

Your example was confusing because you're using some alias that don't exist.

Try to use LEFT JOIN.

with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as 
(select 2 as personId)


select alias1.Id as personId, count(alias2.personId)
from   Table1 as alias1
left   join Table2 as alias2 on alias1.Id = alias2.personId
where  Name = 'Bob'
group  by alias1.Id

answered Nov 29 '18 at 00:08

Erika Madeiros Silva

21
4

Thank you for providing an answer that compiles and executes with a sample data set. – rschoenbach Nov 29 '18 at 00:13

score 1 · Answer 2 · answered Nov 29 '18 at 00:10

1

Please see:

Count Returning blank instead of 0

Essentially, you can't use GROUP BY and expect no results to return a row.

answered Nov 29 '18 at 00:10

Jared C

362
7
19

Empty group by with count returns null

2 Answers2