Re: Making Count(*) return zero

On Dec 11, 6:05 pm, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> 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

Actually, SS2K8 does now include support for the syntax, FWIW:

SELECT ID
FROM (VALUES(51),(53),(55)) AS Foo(ID)

Take Care,
TroyK Received on Wed Dec 16 2009 - 22:56:59 CET