Making Count(*) return zero
Guto
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.
Vadim Tropashko
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.
Bob Badour
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
Gints Plivna
> 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 Plivna
http://www.gplivna.eu
Guto
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.
Bob Badour
The dbms cannot makes something out of nothing. You will need another
relation to join against that has all of the ids.
Hugo Kornelis
(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
TroyK
Actually, SS2K8 does now include support for the syntax, FWIW:
SELECT ID
FROM (VALUES(51),(53),(55)) AS Foo(ID)
Take Care,
TroyK
Norbert_Paul
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