|
Home > Archive > Apache JDO Project > November 2005 > Fwd: COUNT queries
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Fwd: COUNT queries
|
|
| Craig L Russell 2005-11-09, 5:45 pm |
| 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!
| |
| Rick Hillegas 2005-11-09, 5:45 pm |
| 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!
>
>
|
|
|
|
|