Re: Making Count(*) return zero

From: Vadim Tropashko <vadimtro_at_gmail.com>
Date: Wed, 9 Dec 2009 12:27:15 -0800 (PST)
Message-ID: <85f930ed-5516-416b-9d7c-c2122c99b207_at_b36g2000prf.googlegroups.com>


On Dec 9, 12:01 pm, Guto <gutom..._at_gmail.com> wrote:
> 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.

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 oneor  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. Received on Wed Dec 09 2009 - 21:27:15 CET

Original text of this message