Hi,

Is the behavior of COUNT(manager) (please see below) specified in SQL?

> "SELECT COUNT(manager) FROM Employee"

Thanks,

Craig

Begin forwarded message:

> From: Michael Watzek <mwa.tech@spree.de>
> Date: November 9, 2005 7:12:45 AM PST
> To: jdo-dev@db.apache.org
> Subject: COUNT queries
> Reply-To: jdo-dev@db.apache.org
>
>
> Hi,
>
> does COUNT in JDO consider NULL values? In SQL, NULL values are not
> considered by COUNT.
>
> In JDO, does COUNT consider duplicates? In SQL, duplicates are
> considered by COUNT.
>
> Our testdata has five employees. Four employees have the same
> manager. One employee does not have a manager.
>
> What is the result of the following JDO query: "SELECT COUNT
> (manager) FROM Employee"?
>
> Is the result 4?
>
> Regards,
> Michael
> --
> -------------------------------------------------------------------
> Michael Watzek                  Tech@Spree Engineering GmbH
> mailto:mwa.tech@spree.de        Buelowstr. 66
> Tel.:  ++49/30/235 520 36       10783 Berlin - Germany
> Fax.:  ++49/30/217 520 12       http://www.spree.de/
> -------------------------------------------------------------------
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

[ Post a follow-up to this message ]

Hi Craig,

I hope I am answering the correct question here. The behavior of
aggregates is described in Part 2 of the ANSI spec, section 10.9.

1) A null column is excluded from a COUNT( colName ) aggregate. This is
described in the section 10.9 under General Rules 4a. The database is
supposed to raise a warning: "warning--null value eliminated in set
function"

2) Unless you specify the DISTINCT keyword, the COUNT aggregate will not
filter out duplicates. Each row, regardless of whether it is a
duplicate, will go into the tally. This is described in the same section
underGeneral Rules 4b.

For the record, Derby exhibits this ANSI behavior. To summarize:

-- the following query eliminates rows with null in column "a"
select count( a ) from foo;

-- the following query eliminates rows with null in column "a"
-- and eliminates duplicates
select count( distinct a ) from foo;

Cheers,
-Rick

Craig L Russell wrote:

> Hi,
>
> Is the behavior of COUNT(manager) (please see below) specified in SQL?
> 
>
>
> Thanks,
>
> Craig
>
> Begin forwarded message:
> 
>
> Craig Russell
>
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:Craig.Russell-xsfywfwIY+M@public.gmane.org
>
> P.S. A good JDO? O, Gasp!
>
>

[ Post a follow-up to this message ]