Re: Making Count(*) return zero

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 Received on Sat Dec 12 2009 - 01:05:29 CET