Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!212.6.121.138.MISMATCH!newsfeeder.ewetel.de!news.astraweb.com!border1.a.newsrouter.astraweb.com!newsfeed101.telia.com!nf02.dk.telia.net!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
From: Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID>
Newsgroups: comp.databases.theory
Subject: Re: Making Count(*) return zero
Date: Sat, 12 Dec 2009 01:05:29 +0100
Message-ID: <18n5i59m95jq8l306vkfsn3ha8a7eqghm0@4ax.com>
References: <45ecb476-76e7-4c56-940c-8298588d1617@j19g2000yqk.googlegroups.com>  <9e081b75-9f05-434c-8033-9d17568028a8@v25g2000yqk.googlegroups.com> <af1de005-fedb-4d7e-abc4-ec169259cc2f@n35g2000yqm.googlegroups.com>
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 49
NNTP-Posting-Host: 82.161.63.43
X-Trace: 1260576327 news.xs4all.nl 22943 [::ffff:82.161.63.43]:14689
X-Complaints-To: abuse@xs4all.nl
Xref:  news.cambrium.nl

On Thu, 10 Dec 2009 04:12:33 -0800 (PST), Guto wrote:

(snip)
>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

Hi Guto,

As Bob says, you need a table source for these ID values, otherwise the
DBMS can't produce rows for them. But you can include such a source in
your query:

In Microsoft SQL Server, you can use something this:

WITH MyIDs(ID)
 AS (SELECT 51 UNION ALL SELECT 55 UNION ALL SELECT 73)
SELECT     s.ID, COUNT(*)
FROM       Service AS s
INNER JOIN MyIDs AS m
      ON   m.ID = s.ID
WHERE      m.InicialDate IS NULL
OR         m.InicialDate > '20081209'; -- 09/12/2008 is ambiguous!

In other DBMSs, you can use something like this, which is ANSI standard
syntax. I use SQL Server, which does not implement this feature, so I
might have the syntax wrong.

WITH MyIDs(ID)
 AS (VALUES ((51), (53), (73))
SELECT     s.ID, COUNT(*)
FROM       Service AS s
INNER JOIN MyIDs AS m
      ON   m.ID = s.ID
WHERE      m.InicialDate IS NULL
OR         m.InicialDate > '20081209'; -- 09/12/2008 is ambiguous!


Best, Hugo