CODEWITHSUNDEEP
sqlsql-server
J.Doe
J.Doe

Reputation: 31

Empty group by with count returns null

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

Upvotes: 0

Views: 1719

Answers (2)

Jared C
Jared C

Reputation: 372

Please see:

Count Returning blank instead of 0

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

Upvotes: 1

Erika Madeiros Silva
Erika Madeiros Silva

Reputation: 21

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

Upvotes: 1

Related Questions