Re: Making Count(*) return zero

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 10 Dec 2009 11:27:12 -0400
Message-ID: <4b211352$0$5340$9a566e8b_at_news.aliant.net>


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.

The dbms cannot makes something out of nothing. You will need another relation to join against that has all of the ids.

-- 
is there something in it for them, like maybe bailouts, if they can 
panic us into doing something politically to cover them?

November 19, 2007 - John S Bolton

http://tinyurl.com/y9e4vxh
Received on Thu Dec 10 2009 - 16:27:12 CET

Original text of this message