Hi There!

is there a way to make the count(*) return zero . because the default
behavoir is that it will skip this value if there is no records so a
simple query like this

select userid,count(*) as count from users where userid in (select val
from sometable)
group by userid

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

Any hints?
Thanks!
Guto.

On Dec 9, 12:01*pm, Guto <gutom... (AT) gmail (DOT) com> wrote:
From "SQL Design Patterns"

<quote><soapbox>Hugh Darwen’s Summarize

Hugh Darwen argued that group by with aggregation is an operator that
requires two arguments, in general. The idea of introducing such an
operator in SQL never caught on. Yet, in each practical situation it
might be useful to double check if writing group by clause as a one-
or two- argument operator is more appropriate.
</soapbox>

In SQL:
select deptno,
(select count(*) from Emp e
where e.deptno = d.deptno)
from Dept d
</quote>

My understanding evolved a little bit since then. One can argue that
aggregation with grouping is domain dependent operation, so that it
would return all the values in the domain, the ones not listed in
input relation with count 0. To make it safe, you can join it with
some other relation. This is how QBQL approaches it.

Guto wrote:

Use a scalar subquery instead of just count(*) to calculate the [count]
column.

--
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

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 Plivna
http://www.gplivna.eu

On Dec 9, 7:41*pm, Gints Plivna <gints.pli... (AT) gmail (DOT) com> wrote:
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.

Guto wrote:
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

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

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

On Dec 11, 6:05*pm, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:
Actually, SS2K8 does now include support for the syntax, FWIW:

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

Take Care,
TroyK

If you use 'group by' then 'count(*)' can never return zero.
This is for mathematical reasons: 'group by' creates
equivalence classes and count(*) returns each
equivalence class' size. An equivalence class, however,
is not empty, hence, count(*) must be greater or equal
to 1.

You might wish to use 'union':

select id,count(*) as idcount
from ...
group by id
union all
select id, 0 as idcount
from ...

(not tested)

Norbert

Guto wrote: