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?
>
>> "SELECT COUNT(manager) FROM Employee"
>
>
> Thanks,
>
> Craig
>
> Begin forwarded message:
>
>> *From: *Michael Watzek <mwa.tech@spree.de <mailto:mwa.tech@spree.de>>
>> *Date: *November 9, 2005 7:12:45 AM PST
>> *To: *jdo-dev@db.apache.org <mailto:jdo-dev@db.apache.org>
>> *Subject: **COUNT queries*
>> *Reply-To: *jdo-dev@db.apache.org <mailto: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!
>
>
|