|
Home > Archive > Apache JDO Project > November 2005 > 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]
|
|
| Michael Watzek 2005-11-09, 5:45 pm |
| 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 L Russell 2005-11-09, 5:45 pm |
| Hi Rick,
That's exactly what I was trying to find out. You just saved me a few
hours beating my head against the SQL spec.
Thanks,
Craig
On Nov 9, 2005, at 11:25 AM, Rick Hillegas wrote:
> 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:
>
>
>
>
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!
|
|
|
|
|