Re: Making Count(*) return zero

If you use 'group by' then 'count(*)' can never return zero. This is for mathematical reasons: 'group by' creates equivalence classes and count(*) returns each equivalence class' size. An equivalence class, however, is not empty, hence, count(*) must be greater or equal to 1.

You might wish to use 'union':

select id,count(*) as idcount
from ...
group by id
union all
select id, 0 as idcount
from ...

(not tested)

Norbert

Guto wrote:
> On Dec 9, 7:41 pm, Gints Plivna<gints.pli..._at_gmail.com> wrote:
>>> if userid 1 has no records . it wont be returned in the query
>>> instead i want it to show zero so
>>> userid count
>>> 1 0
>>> 2 3
>>> 3 1
>>> 4 0
>>
>> Scalar subqueries as already said or left join as follows:
>> select userid, count(sometable.val)
>> from users left join sometable
>> on (users.userid = sometable.val)
>> group by userid
>>
>> Gints Plivnahttp://www.gplivna.eu
>
> Thank you all for your reply, but actually I need to be more precise
> on the issue:
>
> 1) I have a list of ID
> 2) I need to return the count() of these ID even if they are 0
> 3) Its everything on the same table.
>
> My sql sentence is going like this:
> select ID,count(*)
> from Service
> where ID in (51,55,73)
> and (InicialDate is null or InicialDate> '09/12/2008')
> group by ID
> resulting in :
> ID Column2
> ------------ ----------
> 55 11454
> 73 1
>
> What I need is :
>
> ID Column2
> ------------ ----------
> 51 0
> 55 11454
> 73 1
>
>
> Thank you all.
Received on Mon Mar 15 2010 - 16:27:39 CET