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>
>
>
> 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.
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/y9e4vxhReceived on Thu Dec 10 2009 - 16:27:12 CET